Vlookup on more than one sheet

[mg_sv_r]

Is it possible to make vlookup look at other sheets for a matching value when
it does not find a value on the first sheet??

To look on two sheets I tried

=IF(ISNA(VLOOKUP(C6,SHEET1!A:B,1,FALSE)),"",(VLOOKUP(C6,SHEET2!A:B,1,FALSE)))

THis always returns #N/A even for values I know are present. Also I need the
formula to look on sheet1, if it does not find it look on sheet 2 and if it
still does not find it look on sheet3.

Any ideas?

Any help would be very much appreciated.

Regards
John

 

[Mike H]

Try this:-

=IF(ISERROR(VLOOKUP(C6,Sheet1!A:B,1,FALSE)),VLOOKUP(C6,Sheet2!A:B,2,FALSE),(VLOOKUP(C6,Sheet1!A:B,1,FALSE)))

Mike

 

[Pete_UK]

You need to construct your formula like this:

=IF(ISNA(VLOOKUP(C6,SHEET1!A:B,2,FALSE)),IF(ISNA(VLOOKUP(C6,SHEET2!A:B,
2,FALSE)),"",VLOOKUP(C6,SHEET2!A:B,2,FALSE)­),VLOOKUP(C6,SHEET1!A:B,
2,FALSE))

I've assumed you want to bring data back from column B if you find a
match - you had it set to return from column 1.

Hope this helps.

Pete

 

[bj]

with the equation you have your two responses will be #NA or ""
your isna response is the ""
change the equation to
=IF(ISNA(VLOOKUP(C6,SHEET1!A:B,1,FALSE)),VLOOKUP(C6,SHEET2!A:B,1,FALSE),VLOOKUP(C6,SHEET1!A:B,1,FALSE))

to look at a third sheet
=IF(ISNA(VLOOKUP(C6,SHEET1!A:B,1,FALSE)),IF(ISNA(VLOOKUP(C6,SHEET2!A:B,1,FALSE)),VLOOKUP(C6,SHEET3!A:B,1,FALSE),VLOOKUP(C6,SHEET2!A:B,1,FALSE)),VLOOKUP(C6,SHEET1!A:B,1,FALSE))

 

[Teethless mama]

Try this:

=IF(SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!A:A"),C6)),C6,"not found")

 

[L. Howard Kittle]

Hi John,

I got this from Peo Sjoblom in 2006. If you are looking across three sheets
now you may need to look across several latter. The example Peo offered
looks across 8 worksheets but it could be any number of sheets. I don't
pretend to fully understand the formula, (even with a fairly detailed
explanation of each aspect of the formula Peo e-mailed me). You will need
to make small modifications to suit your workbook and sheets.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

This is an Array Formula so use Ctrl + Shift + Enter to commit.

Where... A2 is the cell with the lookup value on the sheet with the formula.

Where... MySheets is a named range of a list of all the sheet names you want
to lookup. So, somewhere on the formula sheet you would list all the
worksheets of interest, select that list and name it MySheets or whatever
name you want.

Where... "MySheets&"'!A2:A200" is the left most column on all the
worksheets. Yours may be B1:B350 or A1:A1500, just depends on how long your
list is. (This is NOT the Table_Array)

Where... A2:C200 is the Table_Array. Again, adjust to suit your sheets.
This one has three columns, yours may have only 2. If only two columns then
change ...A2:C200"),3,0) to ...A2:B200"),2,0).

HTH
Regards,
Howard