Lookup with 2 columns of data to match

[Heather]

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]

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]

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]

=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]

Thank you Dave!!!! This was a big help!

=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, ...

 

[S D]

Hello Dave, I used your previous post create and Index Match function so that when I have an exact match in two columns it returns the value in the third.
I have it set up right now so Column A="ABC" and Column B=1 it returns the value in column D.

Is there a way to format it so two conditions must be met, but the second condition could have eihter of two criteria?
For example: a way that Column A="ABC" and Column B=(1 OR "xyz")?
Another way I could format is to create another column so:
A1:A500="ABC" AND (B1:B500=1 OR C1:C500="xyz")

Bascially I need it to match ABC and then one of the following: 1, xyz.

Any help would be VERY much appreciated... many hours spent struggling with this one!
Thanks!

 

[Bob Phillips]

=INDEX(D220,MATCH(1,(A2:A20="ABC")*((B2:B20=1)+(C2:C20="xyz")),0))

 

[Dave Peterson]

I bet you meant something like:

=INDEX(D220,MATCH(1,(A2:A20="ABC")*(((B2:B20=1)+(C2:C20="xyz"))>0),0))

Still an array formula.

(Just in case column B = 1 and column C = xyz at the same time.)