Lookup Function??

[Brucea]

Am I doing something wrong? I am using the lookup function to determine some text in a cell. Some of the values come back correctly and others do not. Some times I also get a #N/A result.
What I was hoping for was if the cell (B6) has a particular word in it, then the cell this formula is in would show the abreviated name. I could use a number of nested IF formulas or some VB code but this seems more straight forward.

 

[onlyadrafter]

Hello,

are you looking for exact matches

i.e. =VLOOKUP(A1, C1:D30,2,0)

 

[mosiki]

The lookup function requires the range be ordered from lowest to highest or the other way around, anyway its not a great one to be using.

Other functions are better such as VLOOKUP, or combining MATCH & INDEX.

If you want to post an example I'll have a look for ya.

Mosiki

 

[onlyadrafter]

The lookup function requires the range be ordered from lowest to highest or the other way around, anyway its not a great one to be using.

No is doesn't, if you are looking for exact matches.

 

[Brucea]

Sorry ,I meant to copy the formula into the message I have put the complete post in below. Thanks

Am I doing something wrong? I am using the lookup function to determine some text in a cell. Some of the values come back correctly and others do not. Sometimes I also get a #N/A result.
What I was hoping for was if the cell (B6) has a particular word in it, then the cell this formula is in would show the abbreviated name. I could use a number of nested IF formulas or some VB code but this seems more straight forward.
Here is the formula I am using:
=LOOKUP(B7,{"Ceramic Tile","Cork","Wood","Area Rugs","Laminates","Carpet"},{"CT","CK ","WD ","AR","LM ","Car"})

 

[onlyadrafter]

Hello,

try

=LOOKUP(B7,{"Area Rugs","Carpet","Ceramic Tile","Cork","Laminates","Wood"},{"AR","Car","CT","CK ","LM ","WD "})

they do need to be in order.

 

[mosiki]

If I understand correctly, wherever you find words such as Ceramic Tile, Cork, Wood, etc you would like the cell next to it to show CT, CK, WD ??

If this is the case why not do a simple key table.

eg. A1 = Ceramic Tile A2= CT
B1 = Cork B2= CK
C1 = Wood C2 = WD.....etc


If cell B6 is "Ceramic Tile" in cell B7 put =VLOOKUP(B6,$A$1:$C$2,2,false)

Mosiki

 

[Brucea]

That has got it! Thanks for the help!