Vlookup on multiple sheets

  • Thread starter Thread starter kellonjames
  • Start date Start date
K

kellonjames

I am copying a database fron access into excel; due to the size
(100000+ lines) i have to put it on two excel sheets.
i will like to do a vlookup that will search both sheets for the
result i am looking for.

can someone please help
 
You need something like:

=IF(ISNA(VLOOKUP($A1,Sheet1!A:F,2,0)),IF(ISNA(VLOOKUP($A1,Sheet2!A:F,
2,0)),"not present",VLOOKUP($A1,Sheet2!A:F,2,0)),VLOOKUP($A1,Sheet1!
A:F,2,0))

Looks in Sheet1 first for an exact match on A1 in column A, and if not
found then looks in Sheet2 for an exact match. If A1 is not in either
sheet, returns "not present". All one formula - be wary of spurious
line-breaks.

Lookup table assumed to be 6 columns wide - can be copied across, but
change the 2 to 3, 4, 5, 6 depending on the column (or use COLUMN(B1)
instead of 2).

Hope this helps.

Pete
 
Sorry, you would also need to lock the columns of the lookup table if
copying across, like this:

=IF(ISNA(VLOOKUP($A1,Sheet1!$A:$F,2,0)),IF(ISNA(VLOOKUP($A1,Sheet2!$A:
$F,
2,0)),"not present",VLOOKUP($A1,Sheet2!$A:$F,
2,0)),VLOOKUP($A1,Sheet1!
$A:$F,2,0))

Hope this helps.

Pete
 
Back
Top