Vlookup formula combining two columns

  • Thread starter Thread starter casdaq
  • Start date Start date
C

casdaq

I'm trying to create a formula that takes into account two columns B & C and
matches those two coloums with two columns from another table in A & B and if
those match returns what result is listed in column E. Example below...

Book1
B TEST
C PASS

BOOK2
A TEST
B PASS
E 100.00

BOOK1
D = BOOK2 column E "100.00"

My thought is a "Vlookup" or "If" formula would work, just not sure how to
combine the two columns.

Thank you in advance.
 
Need more info.

Will there be duplicate "matches"?

If so, will you want the value(s) in Column E totaled?

If not, are there other criteria (names, ID numbers) that will/can be used
to differentiate between the (duplicate) rows?
 
You have a few choices...

One is to insert a new column A in the table worksheet.

Then you could concatenate the values in the new column B and C into column A.

=b2&"|"&c2
(and drag down)

Then you could modify the =vlookup() to look at this new column--but concatenate
the year and name into the value to match:

=vlookup(a2&"|"&b2,othersheet!a:e,3,false)
where a2 holds the year and b2 holds the name.

There are other ways, too.

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))
 
Back
Top