Is it possible to do a vlookup across three seperate spreadsheets?

Not sure if there is an easier way to do it than to use 3 vlookups. Assuming
the value to look up is in A1, the lookup tables are in cells A1:A3 of
Sheet2, Sheet3, and Sheet4:

=IF(ISNUMBER(MATCH(A1,Sheet2!A1:A3,0)),VLOOKUP(A1, Sheet2!A1:B3,2,0),IF(ISNUMBER(MATCH(A1,Sheet3!A1:A 3,0)),VLOOKUP(A1,Sheet3!A1:B3,2,0),IF(ISNUMBER(MAT CH(A1,Sheet4!A1:A3,0)),VLOOKUP(A1,Sheet4!A1:B3,2,0 ),"")))


"Christine" wrote:

Is it possible to do a vlookup across three seperate spreadsheets?

=IF(AND(ISNA(VLOOKUP(A1,Sheet3!F:G,2,2)),ISNA(VLOO KUP(A1,Sheet2!F:G,2,2))),V
LOOKUP(A1,Sheet1!F:G,2,2),IF(ISNA(VLOOKUP(A1,Sheet 3!F:G,2,2)),VLOOKUP(A1,She
et2!F:G,2,2),VLOOKUP(A1,Sheet3!F:G,2,2)))

Change the parameters as required......
..........all on one line, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3


"Christine" wrote in message
...
Is it possible to do a vlookup across three seperate spreadsheets?

Slightly shorter and fewer nested function calls

=IF(COUNTIF(Sheet2!A1:A3,A1),VLOOKUP(A1,Sheet2!A1: B3,2,0),IF(COUNTIF(Sheet3!A1:A3,A1),VLOOKUP(A1,She et3!A1:B3,2,0),IF(COUNTIF(Sheet4!A1:A3,A1),VLOOKUP (A1,Sheet4!A1:B3,2,0),"")))


If the data to be returned is numeric, you could try

=MIN(IF(Sheet2!A1:A3=A1,Sheet2!B1:B3),IF(Sheet3!A1 :A3=A1,Sheet3!B1:B3),IF(Sheet4!A1:A3=A1,Sheet4!B1: B3))

array entered using Cntrl+Shift+Enter


"JMB" wrote:

Not sure if there is an easier way to do it than to use 3 vlookups. Assuming
the value to look up is in A1, the lookup tables are in cells A1:A3 of
Sheet2, Sheet3, and Sheet4:

=IF(ISNUMBER(MATCH(A1,Sheet2!A1:A3,0)),VLOOKUP(A1, Sheet2!A1:B3,2,0),IF(ISNUMBER(MATCH(A1,Sheet3!A1:A 3,0)),VLOOKUP(A1,Sheet3!A1:B3,2,0),IF(ISNUMBER(MAT CH(A1,Sheet4!A1:A3,0)),VLOOKUP(A1,Sheet4!A1:B3,2,0 ),"")))


"Christine" wrote:

Is it possible to do a vlookup across three seperate spreadsheets?

This formula works perfect, thank you so much for your help. I came across
one small glitch. On some of the cells where I use this formula I get an
answer of:
1/0/1900 when it should just be a blank cell (#N/A)

Is there something I'm missing?

"JMB" wrote:

Not sure if there is an easier way to do it than to use 3 vlookups. Assuming
the value to look up is in A1, the lookup tables are in cells A1:A3 of
Sheet2, Sheet3, and Sheet4:

=IF(ISNUMBER(MATCH(A1,Sheet2!A1:A3,0)),VLOOKUP(A1, Sheet2!A1:B3,2,0),IF(ISNUMBER(MATCH(A1,Sheet3!A1:A 3,0)),VLOOKUP(A1,Sheet3!A1:B3,2,0),IF(ISNUMBER(MAT CH(A1,Sheet4!A1:A3,0)),VLOOKUP(A1,Sheet4!A1:B3,2,0 ),"")))


"Christine" wrote:

Is it possible to do a vlookup across three seperate spreadsheets?

Hi

In your table(s), column B contains some blanks.
A blank cell (with a date format will be treated as 1/0/1900.

Replace the blank cells in the tables with =NA() if you wish #N/A to be
returned.

--
Regards

Roger Govier


"Christine" wrote in message
...
This formula works perfect, thank you so much for your help. I came
across
one small glitch. On some of the cells where I use this formula I get
an
answer of:
1/0/1900 when it should just be a blank cell (#N/A)

Is there something I'm missing?

"JMB" wrote:

Not sure if there is an easier way to do it than to use 3 vlookups.
Assuming
the value to look up is in A1, the lookup tables are in cells A1:A3
of
Sheet2, Sheet3, and Sheet4:

=IF(ISNUMBER(MATCH(A1,Sheet2!A1:A3,0)),VLOOKUP(A1, Sheet2!A1:B3,2,0),IF(ISNUMBER(MATCH(A1,Sheet3!A1:A 3,0)),VLOOKUP(A1,Sheet3!A1:B3,2,0),IF(ISNUMBER(MAT CH(A1,Sheet4!A1:A3,0)),VLOOKUP(A1,Sheet4!A1:B3,2,0 ),"")))


"Christine" wrote:

Is it possible to do a vlookup across three seperate spreadsheets?

I have a cloumn where I have used the vlookup formula. I also need to to have
a sum formula for this column. Some of the cells have a #N/A answer at the
moment which makes the end sum #N/A. Is there a formula I can use that will
add together whatever numbers I do have show up through the vlookup formula?

"CLR" wrote:

=IF(AND(ISNA(VLOOKUP(A1,Sheet3!F:G,2,2)),ISNA(VLOO KUP(A1,Sheet2!F:G,2,2))),V
LOOKUP(A1,Sheet1!F:G,2,2),IF(ISNA(VLOOKUP(A1,Sheet 3!F:G,2,2)),VLOOKUP(A1,She
et2!F:G,2,2),VLOOKUP(A1,Sheet3!F:G,2,2)))

Change the parameters as required......
..........all on one line, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3


"Christine" wrote in message
...
Is it possible to do a vlookup across three seperate spreadsheets?

Hi Christine

One way
=SUMIF(A:A,"<#N/A")
Change A:A to whatever column or range you want.
--
Regards

Roger Govier


"Christine" wrote in message
...
I have a cloumn where I have used the vlookup formula. I also need to
to have
a sum formula for this column. Some of the cells have a #N/A answer at
the
moment which makes the end sum #N/A. Is there a formula I can use that
will
add together whatever numbers I do have show up through the vlookup
formula?

"CLR" wrote:

=IF(AND(ISNA(VLOOKUP(A1,Sheet3!F:G,2,2)),ISNA(VLOO KUP(A1,Sheet2!F:G,2,2))),V
LOOKUP(A1,Sheet1!F:G,2,2),IF(ISNA(VLOOKUP(A1,Sheet 3!F:G,2,2)),VLOOKUP(A1,She
et2!F:G,2,2),VLOOKUP(A1,Sheet3!F:G,2,2)))

Change the parameters as required......
..........all on one line, watch out for word-wrap

Vaya con Dios,
Chuck, CABGx3


"Christine" wrote in message
...
Is it possible to do a vlookup across three seperate spreadsheets?