Hi, help me with this please

=IF(B10="","",(VLOOKUP(B10,((ArtA!A2:C55000)(ArtB! A2:C49424)),3,FALSE)))

this should return value from 3rd column. It works with only one
sheet(ArtA), but when i try to include 2nd sheet (ArtB) it returns
'#REF!' error.
Question should probably be:" How to include 2nd sheet in VLOOKUP
function? "

Thanks


--
Mare
------------------------------------------------------------------------
Mare's Profile: http://www.excelforum.com/member.php...o&userid=27644
View this thread: http://www.excelforum.com/showthread...hreadid=492126

Hi Mare,

Question should probably be:" How to include 2nd sheet in VLOOKUP
function? "

You can't. You can combine two VLOOKUP functions like this though:

=IF(ISERROR(VLOOKUP(B10,SheetA...)),VLOOKUP(B10,Sh eetB...),VLOOKUP(B10,
SheetA...))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi

Jan Karel has posted you a solution to look in ArtB if the data is not found
in ArtA.
I interpreted your request slightly differently and maybe incorrectly) as
you wanting to add both values to the cell where the formula is placed.
If so then
=IF(B10="","",VLOOKUP(B10,ArtA!A2:C55000,3,FALSE)+
VLOOKUP(B10,ArtA!A2:C55000,3,FALSE))

Regards

Roger Govier


Mare wrote:
Hi, help me with this please

=IF(B10="","",(VLOOKUP(B10,((ArtA!A2:C55000)(ArtB! A2:C49424)),3,FALSE)))

this should return value from 3rd column. It works with only one
sheet(ArtA), but when i try to include 2nd sheet (ArtB) it returns
'#REF!' error.
Question should probably be:" How to include 2nd sheet in VLOOKUP
function? "

Thanks

Hi Jan, Roger

thank You both for promt replyies.

Jan's solution should work for me, because I need VLOOKUP to look in
sheetB if it does not find data in sheetA, but... I can't make it to
work this is what I made of it:
=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C5500 0,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))


This only returns data from "ArtA" and returns '#n/a' when quired data
is in "ArtB".


It is probably obvious that I have started to use Excel today.:)


--
Mare
------------------------------------------------------------------------
Mare's Profile: http://www.excelforum.com/member.php...o&userid=27644
View this thread: http://www.excelforum.com/showthread...hreadid=492126

Hi Mare,

=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C5500 0,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))

Make that:

=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000,3,FALSE)),V LOOKUP(B10,ArtA!A2:C55000,3,FALSE),VLOOKUP(B10,Ar
tB!A2:C49424,3,FALSE))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Mare

Then Jan gave you the correct solution.
You need to use his formula in the style he posted.
Try
=IF(B10="","",IF(ISERROR(VLOOKUP(B10,ArtA!A2:C5500 0,3 FALSE)),
VLOOKUP(B10,ArtB!A2:C55000,3,FALSE),VLOOKUP(B10,Ar tB!A2:C49424,3,FALSE)))


Regards

Roger Govier


Mare wrote:
Hi Jan, Roger

thank You both for promt replyies.

Jan's solution should work for me, because I need VLOOKUP to look in
sheetB if it does not find data in sheetA, but... I can't make it to
work this is what I made of it:
=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C5500 0,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))


This only returns data from "ArtA" and returns '#n/a' when quired data
is in "ArtB".


It is probably obvious that I have started to use Excel today.:)

My apologies Jan,

I had not seen you had already posted the answer to Mare until just after I
had hit the send button.

Regards

Roger Govier


Jan Karel Pieterse wrote:
Hi Mare,


=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C55 000,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))


Make that:

=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000,3,FALSE)),V LOOKUP(B10,ArtA!A2:C55000,3,FALSE),VLOOKUP(B10,Ar
tB!A2:C49424,3,FALSE))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Roger,

I had not seen you had already posted the answer to Mare until just after I
had hit the send button.

No problem!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Thank You Jan,
Thank You Roger,


Problem solved


--
Mare
------------------------------------------------------------------------
Mare's Profile: http://www.excelforum.com/member.php...o&userid=27644
View this thread: http://www.excelforum.com/showthread...hreadid=492126