Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Match / Index off two "keys"
I need a way to find a value (could be text) in column C based on the values in column A and column B. Entries in A and entires in B are not unique by themselves; however, by "keying" off columns A and B together, only one entry in column C results. Example: A-----B-----C D-----X-----Test1 D-----Y-----Test2 F-----Y-----Test3 I've tried various index and match combinations, but haven't figured it out yet. Any ideas? Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=466313 |
#2
|
|||
|
|||
The easiest way is to create a "helper" column concatinating columns A and B.
The items in the new column would look like: DX DY FY This way you can use a simple MATCH and INDEX or VLOOKUP as you please. This technique is not limited to two columns by the way. Good Luck -- Gary''s Student "carlyman" wrote: I need a way to find a value (could be text) in column C based on the values in column A and column B. Entries in A and entires in B are not unique by themselves; however, by "keying" off columns A and B together, only one entry in column C results. Example: A-----B-----C D-----X-----Test1 D-----Y-----Test2 F-----Y-----Test3 I've tried various index and match combinations, but haven't figured it out yet. Any ideas? Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=466313 |
#3
|
|||
|
|||
try sumproduct()
=sumproduct(--(rangeA=criteria 1),--(rangeB=criteria 2),RangeC) the --( changes the logic true false to a 1 0 numeric the arrays in each section need to be the same size but the shorthand for a full column or row will not work (A:A won't work) "carlyman" wrote: I need a way to find a value (could be text) in column C based on the values in column A and column B. Entries in A and entires in B are not unique by themselves; however, by "keying" off columns A and B together, only one entry in column C results. Example: A-----B-----C D-----X-----Test1 D-----Y-----Test2 F-----Y-----Test3 I've tried various index and match combinations, but haven't figured it out yet. Any ideas? Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=466313 |
#4
|
|||
|
|||
Hi,
Try the following formula in say F2; the assumes that the data are in rows 2 thru 1000, and, D2 and E2 are the search criteria. =OFFSET(C1,SUMPRODUCT(--(A2:A1000=D2)*--(B2:B1000=E2)*ROW(C2:C1000))-1,0) Regards, B. R. Ramachandran "carlyman" wrote: I need a way to find a value (could be text) in column C based on the values in column A and column B. Entries in A and entires in B are not unique by themselves; however, by "keying" off columns A and B together, only one entry in column C results. Example: A-----B-----C D-----X-----Test1 D-----Y-----Test2 F-----Y-----Test3 I've tried various index and match combinations, but haven't figured it out yet. Any ideas? Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=466313 |
#5
|
|||
|
|||
Try...
=INDEX(C1:C3,MATCH(1,(A1:A3="D")*(B1:B3="Y"),0)) or =INDEX(C1:C3,MATCH(1,(A1:A3=E1)*(B1:B3=F1),0)) ....where E1 contains your first criterion, such as 'D', and F1 contains your second criterion, such as 'Y'. Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , carlyman wrote: I need a way to find a value (could be text) in column C based on the values in column A and column B. Entries in A and entires in B are not unique by themselves; however, by "keying" off columns A and B together, only one entry in column C results. Example: A-----B-----C D-----X-----Test1 D-----Y-----Test2 F-----Y-----Test3 I've tried various index and match combinations, but haven't figured it out yet. Any ideas? Thanks, JC |
#6
|
|||
|
|||
Hi,
It is a clever idea to concatenate two (or more) columns before using VLOOKUP etc. However, one has to be careful: eg, "donald" & "rich" and "don" & "aldrich" concatenate identically (unless you include a differentiating feature, e.g., a space between the two substrings, while concatenating). Regards, B. R. Ramachandran "Gary''s Student" wrote: The easiest way is to create a "helper" column concatinating columns A and B. The items in the new column would look like: DX DY FY This way you can use a simple MATCH and INDEX or VLOOKUP as you please. This technique is not limited to two columns by the way. Good Luck -- Gary''s Student "carlyman" wrote: I need a way to find a value (could be text) in column C based on the values in column A and column B. Entries in A and entires in B are not unique by themselves; however, by "keying" off columns A and B together, only one entry in column C results. Example: A-----B-----C D-----X-----Test1 D-----Y-----Test2 F-----Y-----Test3 I've tried various index and match combinations, but haven't figured it out yet. Any ideas? Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=466313 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index and Match issues | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |