Lookup with 2 columns of data to match
Thank you Dave!!!! This was a big help!
"Dave Peterson" wrote:
=INDEX(I3:I5,MATCH(1,(A3=G3:G5)*(B2=H3:H5),0))
(still an array formula)
The 0 as the 3rd argument in the match is required. And since you want the 3rd
column, you can just use =index(i3:i5, ...
Heather wrote:
I couldn't get it to work .. here's a snapshot of what I tried
In excel it shows the curly brackets
=INDEX(G3:I5,MATCH(1,(A3=G3:G5)*(B2=H3:H5),3))
REFERENCE Table
COL.a COL.b RESULT COL.a COL.b RESULT
A 0.25 #REF! A 0.25 100
B 0.26 B 0.26 125
C 0.27 C 0.27 150
"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))
Heather wrote:
Hi!
I have 2 columns of data that need to be looked up in a reference table
For example
Column A Column B Result
A .25 100
B .27 150
The result column is being fed by a reference table matching up Column A & B
.. and we don't want to add Columns A & B together because their are numbers
actually in there and want to keep them separate
can anyone help??? Thank you!!!
--
Dave Peterson
--
Dave Peterson
|