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

Advertisements

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
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Two-Column Lookup 2
two-column lookup 7
Lookup Two Columns 5
Lookup two different columns 10
Difficult Two Column Lookup 0
Difficult Two Column Lookup 2
Lookup Two Columns - Again 5
lookup using two columns 3

Top