Long Formula
 

I got some help earlier with an IF Statement which was very helpful from Mike
H. The idea I am going for now is if I have the following ("Wireless
Laptop","Desktop Computer","Flatscreen Monitor","Server","Solar
Panels","I-Phone","I-Pod","Dual Screen Monitor Stand","Wirless Router") I
will get one of the following associated acronyms.
("JERA","SHJK","MCJM","MOPQ","ABPE","POMZ","PDAI", "COPN","HLMW"). In other
words the 1st item on the 1st list needs to match the 1st acroynm on the 2nd
list


Here is the original formula
=LOOKUP(B7,{"H","K","M","P"},{"JER","SHJ","MCJ","M OP"}) that Mike H helped me
with. I wanted to try chaning it around for a different use. Here is what I
have now =LOOKUP(D15,{"Wireless Laptop","Desktop Computer","Flatscreen
Monitor","Server","Solar Panels","I-Phone","I-Pod","Dual Screen Monitor
Stand","Wirless
Router"}{"JERA","SHJK","MCJM","MOPQ","ABPE","POMZ" ,"PDAI","COPN","HLMW"})
some of them work and some of them don't and I couldn't figure out why.
Any thoughts?

Long Formula
 

The first group, called the lookup_vector, *must* be sorted in ascending
order. However, with that many variables your best bet is to create a 2
column table with the item in the left column and the corresponding acroynm
in the right column. It isn't necessary that this table be sorted.

.....................A........................B
1.....Wireless Laptop..........JERA
2.....Desktop Computer.....SHJK
3.....Flatscreen Monitor......MCJM

Then you'd use a formula like this:

=VLOOKUP(D15,A1:B3,2,0)

--
Biff
Microsoft Excel MVP


"Workbook" wrote in message
...
I got some help earlier with an IF Statement which was very helpful from
Mike
H. The idea I am going for now is if I have the following ("Wireless
Laptop","Desktop Computer","Flatscreen Monitor","Server","Solar
Panels","I-Phone","I-Pod","Dual Screen Monitor Stand","Wirless Router") I
will get one of the following associated acronyms.
("JERA","SHJK","MCJM","MOPQ","ABPE","POMZ","PDAI", "COPN","HLMW"). In
other
words the 1st item on the 1st list needs to match the 1st acroynm on the
2nd
list


Here is the original formula
=LOOKUP(B7,{"H","K","M","P"},{"JER","SHJ","MCJ","M OP"}) that Mike H helped
me
with. I wanted to try chaning it around for a different use. Here is
what I
have now =LOOKUP(D15,{"Wireless Laptop","Desktop Computer","Flatscreen
Monitor","Server","Solar Panels","I-Phone","I-Pod","Dual Screen Monitor
Stand","Wirless
Router"}{"JERA","SHJK","MCJM","MOPQ","ABPE","POMZ" ,"PDAI","COPN","HLMW"})
some of them work and some of them don't and I couldn't figure out why.
Any thoughts?

Thanks for sharing long but useful formula..I was searching for it and finally got the code in this old thread