Help please,what is wrong with this formula?

  • Thread starter Thread starter Mare
  • Start date Start date
M

Mare

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 Mare,
Question should probably be:" How to include 2nd sheet in VLOOKUP
function? "
Click to expand...

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

=IF(ISERROR(VLOOKUP(B10,SheetA...)),VLOOKUP(B10,SheetB...),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
 
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:confused: this is what I made of it:
=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C55000,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.:)
 
Hi Mare,
=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C55000,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))
Click to expand...

Make that:

=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000,3,FALSE)),VLOOKUP(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:C55000,3 FALSE)),
VLOOKUP(B10,ArtB!A2:C55000,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE)))


Regards

Roger Govier
 
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
 
Back
Top