Can vlookup do a multi-column match...if not then what
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 wrote:
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
"Mike H" wrote:
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" wrote:
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
--
Dave Peterson
|