Vlookup formula combining two columns

[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.

 

[RagDyer]

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?

 

[Dave Peterson]

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))