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

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

"hitesh" wrote:

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

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,MA TCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)0), 0))&"'!A2:F58"),2,0)),"",VLOOKUP(C3,INDIRECT("'"&I NDEX(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" wrote in message
...
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

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

pls make the changes.

"L. Howard Kittle" wrote:


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,MA TCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)0), 0))&"'!A2:F58"),2,0)),"",VLOOKUP(C3,INDIRECT("'"&I NDEX(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" wrote in message
...
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

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

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

pls make the changes.

"L. Howard Kittle" wrote:


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,MA TCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)0), 0))&"'!A2:F58"),2,0)),"",VLOOKUP(C3,INDIRECT("'"&I NDEX(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" wrote in message
...
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

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.



Regards,
Howard

"hitesh" wrote in message
...
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