Re: vlookup more than 2 range

Tech-Archive recommends: Fix windows errors by optimizing your registry


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 *** 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" <hitesh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ACECB1C8-926B-4E4A-81A2-B0AA175C8764@xxxxxxxxxxxxxxxx
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,'***
8'!$A$2:$F$58,2,FALSE)),VLOOKUP($C3,'***
9'!$A$2:$F$50,2,FALSE),VLOOKUP($C3,'*** 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


.

Quantcast