Home |
Search |
Today's Posts |
#1
|
|||
|
|||
two-column lookup
A WS has names in col A and multiple blocks with the same name(s) with the
corresponding values in subsequent cols. Here is an example A B C D ------------ 1 a a 1 x match a & 4, should give z 2 b a 2 y 3 c a 4 z 4 5 d b 1 k match b & 4, should give m 6 e b 3 l 7 f b 4 m (the value 4 in col C is the second condition to be matched). For each value in col A, I'd like to find values in col D _IF_ the values in cols B and C also match my conditions. I've tried to build INDEX/MATCH/* formulas, but so far with little success as the dual syntax of INDEX is 'a bit' confusing. Chip Pearson's Lookups page was helpful, but did not have such a case. Bob Phillips' formula, while relevant, is inscrutable to me: "If by chance you mean a double lookup, where you have 2 key columns and you want to match both and get the adjacent value in another column, you can use =INDEX(C1:C100, MATCH(D1&D2,A1:A100&B1:B100,0))"--what is what here? z.entropic |
#2
|
|||
|
|||
Hi!
Here are the formulas that will do what you want: Entered with the key combo of CTRL,SHIFT,ENTER: match a & 4, should give z =INDEX(D1:D7,MATCH("A4",B1:B7&C1:C7,0)) match b & 4, should give m =INDEX(D1:D7,MATCH("B4",B1:B7&C1:C7,0)) What these (and Bob's example) do is to concatenate the lookup values "A" and "4" and to also concatenate the lookup range "B1" and "C1". It would look like this: Lookup value: A4 Lookup range: A1 A2 A4 B1 B3 B4 However, I can't see a correlation with these formulas and where it appears that you want to place the formulas. Based on your posted sample, the formulas look like they are in cells E1 and E5. Biff "z.entropic" wrote in message ... A WS has names in col A and multiple blocks with the same name(s) with the corresponding values in subsequent cols. Here is an example A B C D ------------ 1 a a 1 x match a & 4, should give z 2 b a 2 y 3 c a 4 z 4 5 d b 1 k match b & 4, should give m 6 e b 3 l 7 f b 4 m (the value 4 in col C is the second condition to be matched). For each value in col A, I'd like to find values in col D _IF_ the values in cols B and C also match my conditions. I've tried to build INDEX/MATCH/* formulas, but so far with little success as the dual syntax of INDEX is 'a bit' confusing. Chip Pearson's Lookups page was helpful, but did not have such a case. Bob Phillips' formula, while relevant, is inscrutable to me: "If by chance you mean a double lookup, where you have 2 key columns and you want to match both and get the adjacent value in another column, you can use =INDEX(C1:C100, MATCH(D1&D2,A1:A100&B1:B100,0))"--what is what here? z.entropic |
#3
|
|||
|
|||
Sorry, now I see I wasn't clear enough. Here are a few more details:
1. The number "4", or any other, is a constant that could either be entered into a formula directly, or its value copied from a separate single cell, 2. The blocks of data extend down for hundreds or thousands of lines, 3. the array formula would be in a column inserted between cols A and B and copied by dragging down the first entered cell. 4. importantly, by 'match & 4' I really meant MATCH values 'b' and '4' from the same row, but in different columns'. Hope this clarifies my question. z.entropic "Biff" wrote: Hi! Here are the formulas that will do what you want: Entered with the key combo of CTRL,SHIFT,ENTER: match a & 4, should give z =INDEX(D1:D7,MATCH("A4",B1:B7&C1:C7,0)) match b & 4, should give m =INDEX(D1:D7,MATCH("B4",B1:B7&C1:C7,0)) What these (and Bob's example) do is to concatenate the lookup values "A" and "4" and to also concatenate the lookup range "B1" and "C1". It would look like this: Lookup value: A4 Lookup range: A1 A2 A4 B1 B3 B4 However, I can't see a correlation with these formulas and where it appears that you want to place the formulas. Based on your posted sample, the formulas look like they are in cells E1 and E5. Biff "z.entropic" wrote in message ... A WS has names in col A and multiple blocks with the same name(s) with the corresponding values in subsequent cols. Here is an example A B C D ------------ 1 a a 1 x match a & 4, should give z 2 b a 2 y 3 c a 4 z 4 5 d b 1 k match b & 4, should give m 6 e b 3 l 7 f b 4 m (the value 4 in col C is the second condition to be matched). For each value in col A, I'd like to find values in col D _IF_ the values in cols B and C also match my conditions. I've tried to build INDEX/MATCH/* formulas, but so far with little success as the dual syntax of INDEX is 'a bit' confusing. Chip Pearson's Lookups page was helpful, but did not have such a case. Bob Phillips' formula, while relevant, is inscrutable to me: "If by chance you mean a double lookup, where you have 2 key columns and you want to match both and get the adjacent value in another column, you can use =INDEX(C1:C100, MATCH(D1&D2,A1:A100&B1:B100,0))"--what is what here? z.entropic |
#4
|
|||
|
|||
I re-wrote my example to clarify it even mo
A B C D E 1 4 2 a a 1 o 3 b a 2 p 4 c a 3 q 5 d a 4 r 6 e 7 f b 2 s 8 g b 4 t 9 h b 3 u 10 i b 1 v 11 j 12 k c 5 x 13 l c 1 y 14 m c 2 z 15 n c 4 k In effect, the formula in B2 should give 'r', because C2=A2 AND D5=$B$1 (a VLOOKUP in two columns at the same time, where one value (B1) is a constant). The value obtained in B3 by dragging B2 should be 't' (C7=A3 AND $B$1=4). z.entropic |
#5
|
|||
|
|||
Hi!
3. the array formula would be in a column inserted between cols A and B and copied by dragging down the first entered cell. If that's the case then your data is in the range C:F Entered in B2 as an array with the key combo of CTRL,SHIFT,ENTER: =INDEX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)) Copied down returns: B2 = R B3 = T B4 = K B5:B15 = #N/A If you want to suppress the display of #N/A: =IF(ISNA(MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)),"",IND EX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0))) OR, use the shorter formula together with conditional formatting: Select the range B2:B15 Conditional Formatting Formula is: =ISNA(B2) Set the font color to be the same as the background color. OK out Biff "z.entropic" wrote in message ... I re-wrote my example to clarify it even mo A B C D E 1 4 2 a a 1 o 3 b a 2 p 4 c a 3 q 5 d a 4 r 6 e 7 f b 2 s 8 g b 4 t 9 h b 3 u 10 i b 1 v 11 j 12 k c 5 x 13 l c 1 y 14 m c 2 z 15 n c 4 k In effect, the formula in B2 should give 'r', because C2=A2 AND D5=$B$1 (a VLOOKUP in two columns at the same time, where one value (B1) is a constant). The value obtained in B3 by dragging B2 should be 't' (C7=A3 AND $B$1=4). z.entropic |
#6
|
|||
|
|||
Hi, Biff--thanks for your help and effort, but we're still not there...
Where does the formula use col A where the reference string is? If you look at my last example, I need to take cell A2, find the same strings in block C2:C500, then take cell B$1, search col D for values equal to B$1 and match BOTH A2 and B$1 in the same row, in which the sought value will be in col E. There is no col F. z.entropic "Biff" wrote: Hi! 3. the array formula would be in a column inserted between cols A and B and copied by dragging down the first entered cell. If that's the case then your data is in the range C:F Entered in B2 as an array with the key combo of CTRL,SHIFT,ENTER: =INDEX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)) Copied down returns: B2 = R B3 = T B4 = K B5:B15 = #N/A If you want to suppress the display of #N/A: =IF(ISNA(MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)),"",IND EX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0))) OR, use the shorter formula together with conditional formatting: Select the range B2:B15 Conditional Formatting Formula is: =ISNA(B2) Set the font color to be the same as the background color. OK out Biff "z.entropic" wrote in message ... I re-wrote my example to clarify it even mo A B C D E 1 4 2 a a 1 o 3 b a 2 p 4 c a 3 q 5 d a 4 r 6 e 7 f b 2 s 8 g b 4 t 9 h b 3 u 10 i b 1 v 11 j 12 k c 5 x 13 l c 1 y 14 m c 2 z 15 n c 4 k In effect, the formula in B2 should give 'r', because C2=A2 AND D5=$B$1 (a VLOOKUP in two columns at the same time, where one value (B1) is a constant). The value obtained in B3 by dragging B2 should be 't' (C7=A3 AND $B$1=4). z.entropic |
#7
|
|||
|
|||
Biff, finally and with your valuable guidance I got the syntax right; it's
actually simple! Thanks a lot for your time. The solution for my latest example (maybe the spacing got screwed up in my post) is =INDEX(E$2:E$15,MATCH(A2&B$1,C$2:C$15&D$2:D$15,0)) z.entropic "Biff" wrote: Hi! 3. the array formula would be in a column inserted between cols A and B and copied by dragging down the first entered cell. If that's the case then your data is in the range C:F Entered in B2 as an array with the key combo of CTRL,SHIFT,ENTER: =INDEX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)) Copied down returns: B2 = R B3 = T B4 = K B5:B15 = #N/A If you want to suppress the display of #N/A: =IF(ISNA(MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)),"",IND EX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0))) OR, use the shorter formula together with conditional formatting: Select the range B2:B15 Conditional Formatting Formula is: =ISNA(B2) Set the font color to be the same as the background color. OK out Biff "z.entropic" wrote in message ... I re-wrote my example to clarify it even mo A B C D E 1 4 2 a a 1 o 3 b a 2 p 4 c a 3 q 5 d a 4 r 6 e 7 f b 2 s 8 g b 4 t 9 h b 3 u 10 i b 1 v 11 j 12 k c 5 x 13 l c 1 y 14 m c 2 z 15 n c 4 k In effect, the formula in B2 should give 'r', because C2=A2 AND D5=$B$1 (a VLOOKUP in two columns at the same time, where one value (B1) is a constant). The value obtained in B3 by dragging B2 should be 't' (C7=A3 AND $B$1=4). z.entropic |
#8
|
|||
|
|||
it's actually simple!
Yeah, I know! <vbg maybe the spacing got screwed up in my post Well, not the spacing. I read the table as column A having the values: 1,2,3, 4 etc. when they were actually the row numbers. Doh! But, that happens. Anyhow, glad you got it to work. Biff "z.entropic" wrote in message ... Biff, finally and with your valuable guidance I got the syntax right; it's actually simple! Thanks a lot for your time. The solution for my latest example (maybe the spacing got screwed up in my post) is =INDEX(E$2:E$15,MATCH(A2&B$1,C$2:C$15&D$2:D$15,0)) z.entropic "Biff" wrote: Hi! 3. the array formula would be in a column inserted between cols A and B and copied by dragging down the first entered cell. If that's the case then your data is in the range C:F Entered in B2 as an array with the key combo of CTRL,SHIFT,ENTER: =INDEX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)) Copied down returns: B2 = R B3 = T B4 = K B5:B15 = #N/A If you want to suppress the display of #N/A: =IF(ISNA(MATCH(C2&B$1,D$2:D$15&E$2:E$15,0)),"",IND EX(F$2:F$15,MATCH(C2&B$1,D$2:D$15&E$2:E$15,0))) OR, use the shorter formula together with conditional formatting: Select the range B2:B15 Conditional Formatting Formula is: =ISNA(B2) Set the font color to be the same as the background color. OK out Biff "z.entropic" wrote in message ... I re-wrote my example to clarify it even mo A B C D E 1 4 2 a a 1 o 3 b a 2 p 4 c a 3 q 5 d a 4 r 6 e 7 f b 2 s 8 g b 4 t 9 h b 3 u 10 i b 1 v 11 j 12 k c 5 x 13 l c 1 y 14 m c 2 z 15 n c 4 k In effect, the formula in B2 should give 'r', because C2=A2 AND D5=$B$1 (a VLOOKUP in two columns at the same time, where one value (B1) is a constant). The value obtained in B3 by dragging B2 should be 't' (C7=A3 AND $B$1=4). z.entropic |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Help with SUMIF, INDEX, LOOKUP Please !! | Excel Worksheet Functions | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
Lookup last in column formulas | Excel Discussion (Misc queries) | |||
How to lookup data in a row and column | Excel Discussion (Misc queries) |