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

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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.
 
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
 
Thanks that's a winner

Mike H said:
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
Click to expand...
 
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
 
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
 
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.
 
Excellent answer, very helpful.

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

Thanks again.
 
Back
Top