You could use lots (4-5) =vlookup()'s in your formula:
=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),VLOOKUP(A1,Sh eet2!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet3!A:A,0)),VLOOKUP(A1,She et3!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet4!A:A,0)),VLOOKUP(A1,She et4!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet5!A:A,0)),VLOOKUP(A1,She et5!A:C,2,FALSE),
"missing from all sheets"))))
(all one cell)
You are limited by 7 nested functions, though.
Another way:
=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),"",VLOOKUP(A1, Sheet2!A:C,2,FALSE))
&IF(ISERROR(MATCH(A1,Sheet3!A:A,0)),"",VLOOKUP(A1, Sheet3!A:C,2,FALSE))
&IF(ISERROR(MATCH(A1,Sheet4!A:A,0)),"",VLOOKUP(A1, Sheet4!A:C,2,FALSE))
&IF(ISERROR(MATCH(A1,Sheet5!A:A,0)),"",VLOOKUP(A1, Sheet5!A:C,2,FALSE))
If it doesn't find the value on any sheet, it brings back "". If you're
returning a string, it might work for you.
If you're returning a numeric value:
=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),0,VLOOKUP(A1,S heet2!A:C,2,FALSE))
+IF(ISERROR(MATCH(A1,Sheet3!A:A,0)),0,VLOOKUP(A1,S heet3!A:C,2,FALSE))
+IF(ISERROR(MATCH(A1,Sheet4!A:A,0)),0,VLOOKUP(A1,S heet4!A:C,2,FALSE))
+IF(ISERROR(MATCH(A1,Sheet5!A:A,0)),0,VLOOKUP(A1,S heet5!A:C,2,FALSE))
lehigh46 wrote:
Hi All,
Is it possible to do a VLOOKUP of a list that is 4 or 5 times as long
(273,616 rows long) as there are rows on an Excel sheet.
I could place each length of data on a seperate sheet in the same
wookbook, or I could place all of the dada on the same sheet.
Each length of data is 3 columns wide.
Thanks for your help.
Tom Snyder
--
Dave Peterson
|