Vlookup with 2 reference cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use vlookup with 2 reference cells? For example, I would like to return the value for column 3 if both column 1 and column 2 match, rather than using a single reference cell. Any ideas? Thanks in advance.
 
ctrl/shift/enter:
=INDEX(C:C,MATCH(F1&F2,A1:A1000&B1:B1000,0))
where F1 contains the column A item to match and F2 contains the column B
item to match.
Bob Umlas
Excel MVP


Mike said:
Is it possible to use vlookup with 2 reference cells? For example, I
Click to expand...
would like to return the value for column 3 if both column 1 and column 2
match, rather than using a single reference cell. Any ideas? Thanks in
advance.
 
Mike said:
Is it possible to use vlookup with 2 reference cells? For example, I
Click to expand...
would like to return the value for column 3 if both column 1 and column 2
match, rather than using a single reference cell. Any ideas? Thanks in
advance.

No need to post twice asking the same question. See my reply to your
slightly earlier post.
 
Bob and I have given different answers to this post. In some (perhaps many)
situations, the results will be the same, but not in all situations. I hope
that Bob doesn't think I'm trying to 'score points' by pointing out the
difference.

Bob's formula concatenates the two search values and looks for a match for
this single value in the concatenated columns. My formula keeps the two
values separate and treats them independently. To see the difference,
consider two search values, say 12 and 34. Bob's formula would consider all
of these as matches:
blank and 1234
1 and 234
12 and 34
123 and 4
1234 and blank
whereas mine would only treat 12 and 34 as a match.

If this kind of uncertainty isn't relevant, Bob's formula is certainly
simpler.
 
Back
Top