Excellent answer, very helpful.

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

Thanks again.

"Dave Peterson" wrote:

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