vlookup more than 2 range

[hitesh]

dear

i have a database of three sheets, and i want the vlookup in sheet 4, when i
use this formula =IF(ISNA(VLOOKUP($C3,'sheet
8'!$A$2:$F$58,2,FALSE)),VLOOKUP($C3,'sheet
9'!$A$2:$F$50,2,FALSE),VLOOKUP($C3,'sheet 8'!$A$2:$F$58,2,FALSE)), it work
only for 2 sheets. i want that 3rd sheet also covered by this formula, what i
have to do pls make the necessary changes in formula.

regards
anil

 

[Ken]

Your formula as written is only looking @ WorkSheets 8 & 9

 

[L. Howard Kittle]

Give this a try. I got it from Peo Sjoblom in 2006, modified to you
example. Commit with Ctrl+Shift+Enter, you will see { } added to the
formula. Anytime you modify the formula recommit with Ctrl+Shift+Enter.

=IF(ISNA(VLOOKUP(C3,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0)),"",VLOOKUP(C3,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0))

Where C3 is the lookup value on your sheet 4.
Where MySheets is a named range of a list of the sheet names.
Where A2:F58 are the lookup ranges on each of the three sheets.

You do, however, have six columns in your lookup ranges and returning the
second.

HTH
Regards,
Howard

 

[hitesh]

dear,
thanks for formula, but its not working, i type the formula this way its not
working
=IF(ISNA(VLOOKUP(C3,INDIRECT("'"&INDEX(Sheet2!,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet3!&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0)),"",VLOOKUP(C3,INDIRECT("'"&INDEX(Sheet4!,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheet5!&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0))

pls make the changes.

 

[L. Howard Kittle]

You need to make a list of all the sheet names you want to look up and name
it MySheets.

Then use the formula I offered exactly as it is written.

Use Ctrl+Shift+Enter to commit the formula.

Regards,
Howard

 

[L. Howard Kittle]

Hi Anil,

Did you ever get your formula to work properly? If not, you could send me
an sample workbook and I will try to set it up for you.

(e-mail address removed)

Regards,
Howard