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="DELL",1,IF(A1="GATEWAY",2,IF(A1="HEWLITT", 3,"NOTHING")))


--
killertofu
------------------------------------------------------------------------
killertofu's Profile: http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=512398

If you can live with #N/A with no match

=MATCH(D1,{"DELL","GATEWAY","HEWLETT"},0)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"killertofu" wrote
in message ...

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="DELL",1,IF(A1="GATEWAY",2,IF(A1="HEWLITT", 3,"NOTHING")))


--
killertofu
------------------------------------------------------------------------
killertofu's Profile:
http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=512398

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: http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=512398

Subtract 1 from the match formula?

=MATCH(D1,{"DELL","GATEWAY","HEWLETT"},0)-1

killertofu wrote:

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: http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=512398

--

Dave Peterson

Just subtract 1

=MATCH(D1,{"DELL","GATEWAY","HEWLETT"},0)-1


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"killertofu" wrote
in message ...

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:
http://www.excelforum.com/member.php...o&userid=31024
View this thread: http://www.excelforum.com/showthread...hreadid=512398

Bob Phillips wrote...
Just subtract 1

=MATCH(D1,{"DELL","GATEWAY","HEWLETT"},0)-1
....

And to return to the OP's original specs about returning "Nothing" for
no match,

=INDEX({0;1;2;"Nothing"},SEARCH(D1," Dell Gateway Hewlett
"&D1)/8)

or

=INDEX({0;1;2;"Nothing"},
MATCH(D1,{"Dell","Gateway","Hewlett",""}&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...

Beege


"Harlan Grove" wrote in message
ups.com...
Bob Phillips wrote...
Just subtract 1

=MATCH(D1,{"DELL","GATEWAY","HEWLETT"},0)-1
...

And to return to the OP's original specs about returning "Nothing" for
no match,

=INDEX({0;1;2;"Nothing"},SEARCH(D1," Dell Gateway Hewlett
"&D1)/8)

or

=INDEX({0;1;2;"Nothing"},
MATCH(D1,{"Dell","Gateway","Hewlett",""}&LEFT(D1,{ 0,0,0,32767}),0))