Help please,what is wrong with this formula?

[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

[Jan Karel Pieterse]

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,SheetB...),VLOOKUP(B10,
SheetA...))

Regards,

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

[Roger Govier]

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
>
>

[Mare]

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: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.

[Jan Karel Pieterse]

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))

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

[Roger Govier]

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


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: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.
>
>

[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


Jan Karel Pieterse wrote:
> 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))
>
>
> 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
>

[Jan Karel Pieterse]

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

[Mare]

Thank You Jan,
Thank You Roger,


Problem solved