VLOOKUP 2 text columns

B

Brent

I am looking up data from one worksheet.

Colunmns A B C
1 smith Joe 85
2 smart John 90
3 smith John 100

so the formula from the other worksheet looks like this:
=VLOOKUP("Smith",A1:C3, 3, FALSE)
but it returns 85 what I want is john smith returning 100.
Can anyone help ?
 
D

Domenic

Try...

=INDEX(C1:C3,MATCH(1,(A1:A3="Smith")*(B1:B3="John"),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
A

Aussie CPA

An option would be to insert a column to the left of A and insert the
following formula =concatenate(b1,a1) (FOR the first row). This will give you
a combination of "Johnsmith".
Then you only need to change your vlookup to read
=vlookup("Johnsmith",a1:d3,4,false) to get the right answer.
 
A

Aussie CPA

Hi Domenic,
Out of curiosity I replicated your formula and the spreadsheet.
The formula gives a "#N/A". I thought this strange so I went into formula
editor mode. The actual answer listed for formula result was 100. Do you know
why this would occur.

Thanks,

Adam Wood
PS. I'm using XL 2003
 
A

Aussie CPA

DOH! CTRL-SHIFT-ENTER!


Aussie CPA said:
Hi Domenic,
Out of curiosity I replicated your formula and the spreadsheet.
The formula gives a "#N/A". I thought this strange so I went into formula
editor mode. The actual answer listed for formula result was 100. Do you know
why this would occur.

Thanks,

Adam Wood
PS. I'm using XL 2003
Click to expand...
 
D

D Hilberg

If your data in column C is truly numeric, you can use this:

=sumproduct((A1:A3="smith")*(B1:B3="John")*C1:C3)

- David
 
Top