Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text lookup array and match specific data | Excel Worksheet Functions | |||
LOOKUP two data sets for match - return 1 or 0 - Please help! | Excel Worksheet Functions | |||
match columns and associated data | Excel Discussion (Misc queries) | |||
Lookup across 2 or more columns to match a row | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) |