Help me simplify this function....

K

killertofu

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")))
 
B

Bob Phillips

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)
 
K

killertofu

Is there a way so that the MATCH function starts counting at zero? So
the first value in the array returns 00?
 
D

Dave Peterson

Subtract 1 from the match formula?

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

Bob Phillips

Just subtract 1

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


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
H

Harlan Grove

Bob Phillips wrote...
Just subtract 1

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

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))
 
B

Beege

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
 
Top