# two column lookup

V

#### Vandmic

i'm new to excel & what i'm trying to do is; lookup two cells (A2:B2) in
sheet1 & find the matching data in sheet2 (A2:B20) and return the data in
column "D"

Sheet 1
A B C D
103539 1000800796 2 37500042
103539 1000800797 3
103539 1002055700 4
103539 1000800793 5
103539 1001331388 6
103539 1000926809 7
103539 1002521267 1
103539 1000762584 1
103539 1000762551 2
103539 1000992028 3
103539 1000401463 1

Sheet 2
A B C
103539 1001904703 42500042
103539 1000800796 37500042
103539 1000800797 57500042
103539 1002055700 77500042
103539 1000800793 27500042
103539 1001331388 67500042
103539 1000926809 87500042

D

#### Dave Peterson

Saved from a previous post:

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

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

J

#### Jacob Skaria

Hi Dear, try the below formula in Sheet1 D2.

Please note that the below is a array formula. In cell D2 press function key
F2 to edit and paste the formula. Please make sure the formula goes into one
line. Press Ctrl+Shift+Enter to apply this formula. Once done you can notice
the curly braces in the formula bar. Please try and feedback

=INDEX(Sheet2!C2:C20,MATCH(A2&B2,Sheet2!\$A\$2:\$A\$20&Sheet2!\$B\$2:\$B\$20,0),1)

If this post helps click Yes

V

#### Vandmic

this worked great, thank you so much!

Dave Peterson said:
Saved from a previous post:

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

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html