Can vlookup do a multi-column match...if not then what

[Dave]

I want to do a multi column lookup.
If I Match"

Bob with 2, I would get b
John with 2, I would get e

Will vlookup do this?

Col1 Col2 Col3
Bob 1 a
Bob 2 b
Bob 3 c
John 1 d
John 2 e
John 3 f

 

[ND Pard]

Insert a new column, then concatenate the data that is in the next two columns.

Example: If you insert a new column A, to concatenate the data that is in
columns B and C, row 2, enter the formula:

=B2&" "&C2

Copy the formula down and convert it to values via Copy | Paste-Special ...
Values.

Now you can do a VLookUp on the new column A to return the data.

Good Luck.

 

[Mike H]

Dave,

You would use index - match for that. try this array formula (see below) In
practice i'd use cell references for Bob & 3

=INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[Dave]

Thanks that's a winner

Dave,

You would use index - match for that. try this array formula (see below) In
practice i'd use cell references for Bob & 3

=INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[Scott Smith]

Great example - I was able to copy/modify the equation below and get it to
work. But I don't completely understand why it does work? Specifically, what
does the "1" do? MATCH(1,.

I've tried looking in the excel help and think I understand how to do a
Match function, but can't understand what the "1" is doing in the above/below
formula. I do know if I change the "1" to a "2" or anything else, it doesn't
work.

Thanks in advance for the help.

Scott Smith

 

[John]

Hi Scott
Maybe this will be helpfull to you.
Select the cell with your formula then press and hold the Alt button while you
press the letters >T>U>F . You should see the Evaluate function menu, press the
evaluate button
and see when its avaluating the match function, it converts all the logical
True/False to
1 and 0 .
Its very usefull when working out problems with formulas.
HTH
John

 

[Dave Peterson]

In this expression:

MATCH(1,(A1:A6="Bob")*(B1:B6=3),0)

A1:A6="Bob" will result in an array of 6 True/Falses (depending on the values).
B1:B6=3 will result in an array of 6 true/falses, too.

When you multiply those two arrays, you end up with an array of 6 1's or 0's
(true*true = 1, false*anything =0)

So the match(1,(array of 1's and 0's), 0)
will return the position of the first 1 (true*true).
(the 0 means =match() is looking for an exact match).

In this expression, it'll be the row number of the first match where column A
and column B are Bob and 3.

 

[Scott Smith]

Excellent answer, very helpful.

I am in awe of the in-depth excel knowledge you guys have.

Thanks again.