Lookup value in two columns/cells and return a value from a different column

[Bav_2030]

Hi,

I have two columns with values and want to check both cells in a row (A2 and B2) and depending on the value found return the quantity from sheet 2 column 2

Both corresponding cells (A2 and B2) will not have data but there will be data in either of them.

Row	Column A	Column B
2	Pears
3	Apples
4		Pears
5		Orange
6	Grapes
7		Peach

Sheet 2

Fruits	Quantity
Pears	10
Applies	2
Orange	3
Peach	5
Grapes	4
Kiwi	8

I have tired various Vlookup and with concatenate field as a helper but can't get it to work, any help would be appreciated
=VLOOKUP(A2&B2,'Sheet 2'!A:B,2)

 

[Fluff]

Are the blank cells in the 1st sheet totally empty, or do they have a formula?

 

[Bav_2030]

Are the blank cells in the 1st sheet totally empty, or do they have a formula?

They are totally empty

 

[Fluff]

In that case how about

=VLOOKUP(A2&B2,'Sheet 2'!A:B,2,0)

 

[Bav_2030]

In that case how about

=VLOOKUP(A2&B2,'Sheet 2'!A:B,2,0)

Oh my god, it has worked, I just realised there were some spaces in the blank cells! - thank you for all your help

 

[Fluff]

You're welcome & thanks for the feedback.