VLOOKUP 2 text columns

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 ?

Try...

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

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

Hope this helps!

In article ,
"Brent" wrote:

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 ?

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.


"Brent" wrote:

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 ?

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

"Domenic" wrote:

Try...

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

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

Hope this helps!

In article ,
"Brent" wrote:

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 ?

DOH! CTRL-SHIFT-ENTER!


"Aussie CPA" wrote:

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

"Domenic" wrote:

Try...

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

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

Hope this helps!

In article ,
"Brent" wrote:

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 ?

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

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

- David