VLOOKUP , if not found look use next column in table as the index

[jwalkerack]

Hi there ,

i have a column of data which i am looking up in a table

The data has come from a few different sources. So i can not be 100 percent its not contained in the table , It might just under another name

What i would like to do , is do the first look up , if the value is not found and returns the next look up i would like the vlookup to search again

At the moment my code for the look up is Column 1 is the index column

=VLOOKUP(A2,Short_Names!$A$1:$C$573,1,FALSE)

i like to be able to make column b the index line

=VLOOKUP(A2,Short_Names!$B$1:$C$573,1,FALSE)


Is there a way i can combine an if statment or something so i would be able to check again if not found. Thanks a lot


=VLOOKUP(A2,Short_Names!$A$1:$A$573,1,FALSE), =VLOOKUP(A2,Short_Names!$B$1:$C$573,1,FALSE) , =VLOOKUP(A2,Short_Names!$C$1:$C$573,1,FALSE)

 

[joemeshuggah]

maybe...

=IF(ISERROR(VLOOKUP(A2,Short_Names!$A$1:$A$573,1,FALSE)),IF(ISERROR(VLOOKUP(A2,Short_Names!$B$1:$C$573,1,FALSE)),IF(ISERROR( VLOOKUP(A2,Short_Names!$C$1:$C$573,1,FALSE)),"Not Found",VLOOKUP(A2,Short_Names!$C$1:$C$573,1,FALSE)),VLOOKUP(A2,Short_Names!$B$1:$C$573,1,FALSE)),VLOOKUP(A2,Short_Names!$A$1:$A$573,1,FALSE))

 

[bbott]

=IFERROR(IFERROR(VLOOKUP(A2,Short_Names!$A$1:$A$573,1,0),VLOOKUP(A2,Short_Names!$B$1:$B$573,1,0)),VLOOKUP(A2,Short_Names!$C$1:$C$573,1,0))

 

[jwalkerack]

That seems to work thanks a lot guys

 

[bbott]

Can you post a sample of your data? What is in A2 and what is the expected result?

it was able to find this first two. But did not return values for the other two.

Not sure what this means. Are you trying to return multiple values at the same time?

 

[jwalkerack]

I think my data i was looking up was a little corrupted it contained an extra space so it was not seeing some values.

Just trying to return on value at a time , but i think this has work now. Thanks for your help

Although with in this forumala there is the following A2,Short_Names!$A$1:$A$573,1,0

Is the 1 used for the value to be returned ? What does the zero do , i am not versed with this syntax ? If i wanted to look for a value in one column but return another of the tables columns how would i modify this formula ?

 

[bbott]

=VLOOKUP(A2,Short_Names!$A$1:$A$573,1,0)

The 1 is saying to return the value in the first column. Since the table only references column A in this formula, it can only return the first column.

=VLOOKUP(A2,Short_Names!$A$1:$A$573,1,0)

The 0 tells the formula to look for an exact match. This is equivalent to entering FALSE for the last argument.

 

[Aladin Akyurek]

Try something like...

=IFERROR(INDEX(Short_Names!$C$1:$C$573,LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(A2,Short_Names!$A$1:$A$573,0),MATCH(A2,Short_Names!$B$1:$B$573,0)))),"not found")

This returns an associated result from the corresponding C range.

 

[jwalkerack]

Thanks guys for the replies ... muchly appreciated