MrExcel.com - Your One Stop for Excel Tips & Solutions

VLOOKUP

Posted by Rich on September 14, 2000 9:58 AM

Can the VLOOKUp workbook function be used across multiple worksheets in the same workbook??

Thanks

Posted by Scott H on September 14, 0100 4:34 PM

Unfortunately not. The function needs a contiguous range to work correctly. What you can try is to create a new worksheet that contains all the data in one place. BTW try naming your cell range, and using the name in the vlookup formula (I think it's easier that way).

Posted by Celia on September 14, 0100 5:47 PM

Rich

=IF(ISNA(VLOOKUP("x",A1:B5,2,FALSE)),VLOOKUP("x",Sheet2!A1:B10,2,FALSE),VLOOKUP("x",A1:B5,2,FALSE))

Celia

Posted by Celia on September 14, 0100 6:15 PM

I think a neater way is :-

=VLOOKUP("x",IF(ISNA(VLOOKUP("x",Table1,2,FALSE)),Table2,Table1),2,FALSE))

Table1 is a named range on one sheet and Table2 on another sheet.
To eliminate showing #N/A when there is no match :-

=IF(ISNA(VLOOKUP("x",IF(ISNA(VLOOKUP("x",Table1,2,FALSE)),Table2,Table1),2,FALSE)),"",VLOOKUP("x",IF(ISNA(VLOOKUP("x",Table1,2,FALSE)),Table2,Table1),2,FALSE))

Celia