vlookup more than 2 range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hitesh - 27 May 2008 15:24 GMT

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

Reply to this Message

Ken - 27 May 2008 19:04 GMT

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

> dear
>
[quoted text clipped - 7 lines]
> regards
> anil

Reply to this Message

L. Howard Kittle - 27 May 2008 20:33 GMT

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

> dear
>
[quoted text clipped - 9 lines]
> regards
> anil

Reply to this Message

hitesh - 28 May 2008 10:55 GMT

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.

> 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
[quoted text clipped - 26 lines]
> > regards
> > anil

Reply to this Message

L. Howard Kittle - 28 May 2008 17:32 GMT

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

> dear,
> thanks for formula, but its not working, i type the formula this way its
[quoted text clipped - 36 lines]
>> > regards
>> > anil

Reply to this Message

L. Howard Kittle - 30 May 2008 21:30 GMT

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.

lhkittle@comcast.net

Regards,
Howard

> dear
>
[quoted text clipped - 9 lines]
> regards
> anil

Reply to this Message