VLOOKUP 2 text columns

[Guest]

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 ?

 

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

DOH! CTRL-SHIFT-ENTER!

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

 

[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