Vlookup on multiple sheets

  • Thread starter Thread starter Siny-j
  • Start date Start date
S

Siny-j

Hi ppl,

Well i've just been looking through a lot of sites to find the answer
but i coudn't realy find what i was looking for.

So does any1 know how i can use VLOOKUP on multiple sheets to look up
value ?

this is what i have so far:

for 1 sheet this works:
=VLOOKUP(I24;'2003'!$A$1:$F$250;4;FALSE)

For 2 sheets i got this:
=IF(ISNA(VLOOKUP(I24;'2003'!A1:F250;4;FALSE));(VLOOKUPI24;'2004'!A1:F250;4;FALSE)))

But the one for 2 sheets only gives me a value if the correspondin
value which it should look for is in sheet"2004".


Thx in advance
SIny-
 
...
...
For 2 sheets i got this:
=IF(ISNA(VLOOKUP(I24;'2003'!A1:F250;4;FALSE));(VLOOKUPI24;'2004'!A1:F250;4;FALSE)))

But the one for 2 sheets only gives me a value if the corresponding
value which it should look for is in sheet"2004".
Click to expand...

Unclear what you want. Either you want to determine the row from the '2003'
worksheet but pull a value from the '2004' worksheet, or you want to try looking
up in the '2003' worksheet, and if not found, lookup in the '2004' worksheet. If
the former,

=IF(ISNUMBER(MATCH(I24;'2003'!A1:A250;0));
INDEX('2004'!A1:F250;MATCH(I24;'2003'!A1:F250;0);4);"")

If the latter, the array formula

=VLOOKUP(I24;INDIRECT(INDEX("'"&{"2003";"2004"}&"'",
MATCH(TRUE,COUNTIF(INDIRECT("'"&{"2003";"2004"}&"'!A1:A250");I24)>0;0))
&"'!A1:F250");4;0)

(which doesn't include error trapping).
 
Back
Top