two column lookup
 

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

two column lookup
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Vandmic wrote:

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

--

Dave Peterson

two column lookup
 

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
---------------
Jacob Skaria


"Vandmic" wrote:

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

two column lookup
 

this worked great, thank you so much!

"Dave Peterson" wrote:

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Vandmic wrote:

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

--

Dave Peterson