software

Help me simplify this function into something that I never have to write
again... Thanks, ahead of time...

HERE ARE THE CELLS:
A
1 DELL
2 GATEWAY
3 HEWLITT

HERE IS THE FORMULA FOR B1 THAT NEEDS STREAMLINING:
=IF(A1=quot;DELLquot;,1,IF(A1=quot;GATEWAYquot;,2,IF(A1=quot;HEWLITTquot;, 3,quot;NOTHINGquot;)))--
killertofu
------------------------------------------------------------------------
killertofu's Profile: www.excelforum.com/member.php...oamp;userid=31024
View this thread: www.excelforum.com/showthread...hreadid=512398If you can live with #N/A with no match

=MATCH(D1,{quot;DELLquot;,quot;GATEWAYquot;,quot;HEWLETTquot;},0)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;killertofuquot; gt; wrote
in message ...
gt;
gt; Help me simplify this function into something that I never have to write
gt; again... Thanks, ahead of time...
gt;
gt; HERE ARE THE CELLS:
gt; A
gt; 1 DELL
gt; 2 GATEWAY
gt; 3 HEWLITT
gt;
gt; HERE IS THE FORMULA FOR B1 THAT NEEDS STREAMLINING:
gt; =IF(A1=quot;DELLquot;,1,IF(A1=quot;GATEWAYquot;,2,IF(A1=quot;HEWLITTquot;, 3,quot;NOTHINGquot;)))
gt;
gt;
gt; --
gt; killertofu
gt; ------------------------------------------------------------------------
gt; killertofu's Profile:
www.excelforum.com/member.php...oamp;userid=31024
gt; View this thread: www.excelforum.com/showthread...hreadid=512398
gt;

Is there a way so that the MATCH function starts counting at zero? So
the first value in the array returns 00?--
killertofu
------------------------------------------------------------------------
killertofu's Profile: www.excelforum.com/member.php...oamp;userid=31024
View this thread: www.excelforum.com/showthread...hreadid=512398Subtract 1 from the match formula?

=MATCH(D1,{quot;DELLquot;,quot;GATEWAYquot;,quot;HEWLETTquot;},0)-1

killertofu wrote:
gt;
gt; Is there a way so that the MATCH function starts counting at zero? So
gt; the first value in the array returns 00?
gt;
gt; --
gt; killertofu
gt; ------------------------------------------------------------------------
gt; killertofu's Profile: www.excelforum.com/member.php...oamp;userid=31024
gt; View this thread: www.excelforum.com/showthread...hreadid=512398

--

Dave Peterson

Just subtract 1

=MATCH(D1,{quot;DELLquot;,quot;GATEWAYquot;,quot;HEWLETTquot;},0)-1--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;killertofuquot; gt; wrote
in message ...
gt;
gt; Is there a way so that the MATCH function starts counting at zero? So
gt; the first value in the array returns 00?
gt;
gt;
gt; --
gt; killertofu
gt; ------------------------------------------------------------------------
gt; killertofu's Profile:
www.excelforum.com/member.php...oamp;userid=31024
gt; View this thread: www.excelforum.com/showthread...hreadid=512398
gt;
Bob Phillips wrote...
gt;Just subtract 1
gt;
gt;=MATCH(D1,{quot;DELLquot;,quot;GATEWAYquot;,quot;HEWLETTquot;},0)-1
....

And to return to the OP's original specs about returning quot;Nothingquot; for
no match,

=INDEX({0;1;2;quot;Nothingquot;},SEARCH(D1,quot; Dell Gateway Hewlett
quot;amp;D1)/8)

or

=INDEX({0;1;2;quot;Nothingquot;},
MATCH(D1,{quot;Dellquot;,quot;Gatewayquot;,quot;Hewlettquot;,quot;quot;}amp;LEFT(D1,{ 0,0,0,32767}),0))All,

The OP wanted to simplify so that they never had to write again. We need to
know a little more to help, here. What about the original formula causes a
re-write? I thought the original was fairly simple to begin with...

Beegequot;Harlan Grovequot; gt; wrote in message ups.com...
gt; Bob Phillips wrote...
gt;gt;Just subtract 1
gt;gt;
gt;gt;=MATCH(D1,{quot;DELLquot;,quot;GATEWAYquot;,quot;HEWLETTquot;},0)-1
gt; ...
gt;
gt; And to return to the OP's original specs about returning quot;Nothingquot; for
gt; no match,
gt;
gt; =INDEX({0;1;2;quot;Nothingquot;},SEARCH(D1,quot; Dell Gateway Hewlett
gt; quot;amp;D1)/8)
gt;
gt; or
gt;
gt; =INDEX({0;1;2;quot;Nothingquot;},
gt; MATCH(D1,{quot;Dellquot;,quot;Gatewayquot;,quot;Hewlettquot;,quot;quot;}amp;LEFT(D1,{ 0,0,0,32767}),0))
gt;