Lookup with 2 columns of data to match
 

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

Lookup with 2 columns of data to match
 

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

Lookup with 2 columns of data to match
 

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

Lookup with 2 columns of data to match
 

=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

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

using OR function with Index/Match?
 

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!

using OR function with Index/Match?
 

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

--
__________________________________
HTH

Bob

<S D wrote in message ...
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!

using OR function with Index/Match?
 

I bet you meant something like:

=INDEX(D2:D20,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.)



Bob Phillips wrote:

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

--
__________________________________
HTH

Bob

<S D wrote in message ...
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!

--

Dave Peterson