If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup 2nd & last match with two conditions
Hello,
I've a table below, I would like to setup a query which could return a value showing the 2nd match and last match in the next column. Thank in advance for anyone great help. cheers Billy Column B C D 1st 2nd Row criteria criteria Value 3 A Y 1 4 B Y 2 5 C Y 3 6 A Y 4 7 B X 5 8 C Y 6 9 D X 7 10 A Y 8 11 B Y 9 12 C X 10 query 1 query 2 1st criteria A C 2nd criteria Y Y nth of match 2 last Result 4 6 |
#2
|
|||
|
|||
Lookup 2nd & last match with two conditions
For the 2nd instance...
Array entered** : =INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C 3:C12))),2)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the last instance: =LOOKUP(2,1/((A3:A12="C")*(B3:B12="Y")),C3:C12) -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Hello, I've a table below, I would like to setup a query which could return a value showing the 2nd match and last match in the next column. Thank in advance for anyone great help. cheers Billy Column B C D 1st 2nd Row criteria criteria Value 3 A Y 1 4 B Y 2 5 C Y 3 6 A Y 4 7 B X 5 8 C Y 6 9 D X 7 10 A Y 8 11 B Y 9 12 C X 10 query 1 query 2 1st criteria A C 2nd criteria Y Y nth of match 2 last Result 4 6 |
#3
|
|||
|
|||
Lookup 2nd & last match with two conditions
Biff, it's perfect. Many thanks.
I do not quite understand the beginning part of the last instance, could you please guide me. Cheers, Billy "T. Valko" wrote: For the 2nd instance... Array entered** : =INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C 3:C12))),2)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the last instance: =LOOKUP(2,1/((A3:A12="C")*(B3:B12="Y")),C3:C12) -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Hello, I've a table below, I would like to setup a query which could return a value showing the 2nd match and last match in the next column. Thank in advance for anyone great help. cheers Billy Column B C D 1st 2nd Row criteria criteria Value 3 A Y 1 4 B Y 2 5 C Y 3 6 A Y 4 7 B X 5 8 C Y 6 9 D X 7 10 A Y 8 11 B Y 9 12 C X 10 query 1 query 2 1st criteria A C 2nd criteria Y Y nth of match 2 last Result 4 6 . |
#4
|
|||
|
|||
Lookup 2nd & last match with two conditions
Let's use this example to demonstrate how this works:
........A.....B.....C 1.....A.....Y.....1 2.....B.....Y.....2 3.....C.....Y.....3 4.....A.....Y.....4 5.....B.....X.....5 Return the value in column C that corresponds to the *last instance* of A and Y. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) Result = 4 These expressions will return an array of either TRUE or FALSE: (A1:A5="A") (B1:B5="Y") A1 = A = TRUE A2 = A = FALSE A3 = A = FALSE A4 = A = TRUE A5 = A = FALSE B1 = Y = TRUE B2 = Y = TRUE B3 = Y = TRUE B4 = Y = TRUE B5 = Y = FALSE Those arrays are then multiplied together and the result will be an array of either 1s or 0s: T * T = 1 F * T = 0 F * T = 0 T * T = 1 F * F = 0 We then divide those results by 1. 1 / 1 = 1 0 / 1 = #DIV/0! 0 / 1 = #DIV/0! 1 / 1 = 1 0 / 1 = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;#DIV/0!;1;#DIV/0!},C1:C5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the results of this expression: 1/((A1:A5="A")*(B1:B5="Y") will not return a value greater than 1 ensuring that the lookup_value 2 is greater than any value in the lookup_vector. This is how that would look vertically: Lookup_value = 2 Lookup_Vector.....Result_Vector 1..........1.........................1 2.....#DIV/0!...................2 3.....#DIV/0!...................3 4..........1.........................4 5.....#DIV/0!...................5 The LOOKUP function will "find" the *last* numeric value in the lookup_vector that is less than 2. So, the *last instance* of A & Y was in row 4, A4 & B4. Return the corresponding value from the result_vector cell C4. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) =4 exp101 -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Biff, it's perfect. Many thanks. I do not quite understand the beginning part of the last instance, could you please guide me. Cheers, Billy "T. Valko" wrote: For the 2nd instance... Array entered** : =INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C 3:C12))),2)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the last instance: =LOOKUP(2,1/((A3:A12="C")*(B3:B12="Y")),C3:C12) -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Hello, I've a table below, I would like to setup a query which could return a value showing the 2nd match and last match in the next column. Thank in advance for anyone great help. cheers Billy Column B C D 1st 2nd Row criteria criteria Value 3 A Y 1 4 B Y 2 5 C Y 3 6 A Y 4 7 B X 5 8 C Y 6 9 D X 7 10 A Y 8 11 B Y 9 12 C X 10 query 1 query 2 1st criteria A C 2nd criteria Y Y nth of match 2 last Result 4 6 . |
#5
|
|||
|
|||
Lookup 2nd & last match with two conditions
Ooops!
We then divide those results by 1. 1 / 1 = 1 0 / 1 = #DIV/0! 0 / 1 = #DIV/0! 1 / 1 = 1 0 / 1 = #DIV/0! I've got that backwards! It should be: We then divided 1 by those results: 1 / 1 = 1 1 / 0 = #DIV/0! 1 / 0 = #DIV/0! 1 / 1 = 1 1 / 0 = #DIV/0! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Let's use this example to demonstrate how this works: .......A.....B.....C 1.....A.....Y.....1 2.....B.....Y.....2 3.....C.....Y.....3 4.....A.....Y.....4 5.....B.....X.....5 Return the value in column C that corresponds to the *last instance* of A and Y. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) Result = 4 These expressions will return an array of either TRUE or FALSE: (A1:A5="A") (B1:B5="Y") A1 = A = TRUE A2 = A = FALSE A3 = A = FALSE A4 = A = TRUE A5 = A = FALSE B1 = Y = TRUE B2 = Y = TRUE B3 = Y = TRUE B4 = Y = TRUE B5 = Y = FALSE Those arrays are then multiplied together and the result will be an array of either 1s or 0s: T * T = 1 F * T = 0 F * T = 0 T * T = 1 F * F = 0 We then divide those results by 1. 1 / 1 = 1 0 / 1 = #DIV/0! 0 / 1 = #DIV/0! 1 / 1 = 1 0 / 1 = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;#DIV/0!;1;#DIV/0!},C1:C5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the results of this expression: 1/((A1:A5="A")*(B1:B5="Y") will not return a value greater than 1 ensuring that the lookup_value 2 is greater than any value in the lookup_vector. This is how that would look vertically: Lookup_value = 2 Lookup_Vector.....Result_Vector 1..........1.........................1 2.....#DIV/0!...................2 3.....#DIV/0!...................3 4..........1.........................4 5.....#DIV/0!...................5 The LOOKUP function will "find" the *last* numeric value in the lookup_vector that is less than 2. So, the *last instance* of A & Y was in row 4, A4 & B4. Return the corresponding value from the result_vector cell C4. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) =4 exp101 -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Biff, it's perfect. Many thanks. I do not quite understand the beginning part of the last instance, could you please guide me. Cheers, Billy "T. Valko" wrote: For the 2nd instance... Array entered** : =INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C 3:C12))),2)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the last instance: =LOOKUP(2,1/((A3:A12="C")*(B3:B12="Y")),C3:C12) -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Hello, I've a table below, I would like to setup a query which could return a value showing the 2nd match and last match in the next column. Thank in advance for anyone great help. cheers Billy Column B C D 1st 2nd Row criteria criteria Value 3 A Y 1 4 B Y 2 5 C Y 3 6 A Y 4 7 B X 5 8 C Y 6 9 D X 7 10 A Y 8 11 B Y 9 12 C X 10 query 1 query 2 1st criteria A C 2nd criteria Y Y nth of match 2 last Result 4 6 . |
#6
|
|||
|
|||
Lookup 2nd & last match with two conditions
T. Valko,
Thanks a lot, I really learnt from your illistration. "T. Valko" wrote: Ooops! We then divide those results by 1. 1 / 1 = 1 0 / 1 = #DIV/0! 0 / 1 = #DIV/0! 1 / 1 = 1 0 / 1 = #DIV/0! I've got that backwards! It should be: We then divided 1 by those results: 1 / 1 = 1 1 / 0 = #DIV/0! 1 / 0 = #DIV/0! 1 / 1 = 1 1 / 0 = #DIV/0! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Let's use this example to demonstrate how this works: .......A.....B.....C 1.....A.....Y.....1 2.....B.....Y.....2 3.....C.....Y.....3 4.....A.....Y.....4 5.....B.....X.....5 Return the value in column C that corresponds to the *last instance* of A and Y. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) Result = 4 These expressions will return an array of either TRUE or FALSE: (A1:A5="A") (B1:B5="Y") A1 = A = TRUE A2 = A = FALSE A3 = A = FALSE A4 = A = TRUE A5 = A = FALSE B1 = Y = TRUE B2 = Y = TRUE B3 = Y = TRUE B4 = Y = TRUE B5 = Y = FALSE Those arrays are then multiplied together and the result will be an array of either 1s or 0s: T * T = 1 F * T = 0 F * T = 0 T * T = 1 F * F = 0 We then divide those results by 1. 1 / 1 = 1 0 / 1 = #DIV/0! 0 / 1 = #DIV/0! 1 / 1 = 1 0 / 1 = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;#DIV/0!;1;#DIV/0!},C1:C5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the results of this expression: 1/((A1:A5="A")*(B1:B5="Y") will not return a value greater than 1 ensuring that the lookup_value 2 is greater than any value in the lookup_vector. This is how that would look vertically: Lookup_value = 2 Lookup_Vector.....Result_Vector 1..........1.........................1 2.....#DIV/0!...................2 3.....#DIV/0!...................3 4..........1.........................4 5.....#DIV/0!...................5 The LOOKUP function will "find" the *last* numeric value in the lookup_vector that is less than 2. So, the *last instance* of A & Y was in row 4, A4 & B4. Return the corresponding value from the result_vector cell C4. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) =4 exp101 -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Biff, it's perfect. Many thanks. I do not quite understand the beginning part of the last instance, could you please guide me. Cheers, Billy "T. Valko" wrote: For the 2nd instance... Array entered** : =INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C 3:C12))),2)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the last instance: =LOOKUP(2,1/((A3:A12="C")*(B3:B12="Y")),C3:C12) -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Hello, I've a table below, I would like to setup a query which could return a value showing the 2nd match and last match in the next column. Thank in advance for anyone great help. cheers Billy Column B C D 1st 2nd Row criteria criteria Value 3 A Y 1 4 B Y 2 5 C Y 3 6 A Y 4 7 B X 5 8 C Y 6 9 D X 7 10 A Y 8 11 B Y 9 12 C X 10 query 1 query 2 1st criteria A C 2nd criteria Y Y nth of match 2 last Result 4 6 . . |
#7
|
|||
|
|||
Lookup 2nd & last match with two conditions
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... T. Valko, Thanks a lot, I really learnt from your illistration. "T. Valko" wrote: Ooops! We then divide those results by 1. 1 / 1 = 1 0 / 1 = #DIV/0! 0 / 1 = #DIV/0! 1 / 1 = 1 0 / 1 = #DIV/0! I've got that backwards! It should be: We then divided 1 by those results: 1 / 1 = 1 1 / 0 = #DIV/0! 1 / 0 = #DIV/0! 1 / 1 = 1 1 / 0 = #DIV/0! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Let's use this example to demonstrate how this works: .......A.....B.....C 1.....A.....Y.....1 2.....B.....Y.....2 3.....C.....Y.....3 4.....A.....Y.....4 5.....B.....X.....5 Return the value in column C that corresponds to the *last instance* of A and Y. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) Result = 4 These expressions will return an array of either TRUE or FALSE: (A1:A5="A") (B1:B5="Y") A1 = A = TRUE A2 = A = FALSE A3 = A = FALSE A4 = A = TRUE A5 = A = FALSE B1 = Y = TRUE B2 = Y = TRUE B3 = Y = TRUE B4 = Y = TRUE B5 = Y = FALSE Those arrays are then multiplied together and the result will be an array of either 1s or 0s: T * T = 1 F * T = 0 F * T = 0 T * T = 1 F * F = 0 We then divide those results by 1. 1 / 1 = 1 0 / 1 = #DIV/0! 0 / 1 = #DIV/0! 1 / 1 = 1 0 / 1 = #DIV/0! At this point the formula looks like this: =LOOKUP(2,{1;#DIV/0!;#DIV/0!;1;#DIV/0!},C1:C5) The way that LOOKUP works is if the lookup_value is greater than all the values in the lookup_vector it will match the *last* value in the lookup_vector that is less than the lookup_value. The *last* value in the lookup_vector that is less than the lookup_value is the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2 because we know that the results of this expression: 1/((A1:A5="A")*(B1:B5="Y") will not return a value greater than 1 ensuring that the lookup_value 2 is greater than any value in the lookup_vector. This is how that would look vertically: Lookup_value = 2 Lookup_Vector.....Result_Vector 1..........1.........................1 2.....#DIV/0!...................2 3.....#DIV/0!...................3 4..........1.........................4 5.....#DIV/0!...................5 The LOOKUP function will "find" the *last* numeric value in the lookup_vector that is less than 2. So, the *last instance* of A & Y was in row 4, A4 & B4. Return the corresponding value from the result_vector cell C4. =LOOKUP(2,1/((A1:A5="A")*(B1:B5="Y")),C1:C5) =4 exp101 -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Biff, it's perfect. Many thanks. I do not quite understand the beginning part of the last instance, could you please guide me. Cheers, Billy "T. Valko" wrote: For the 2nd instance... Array entered** : =INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C 3:C12))),2)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the last instance: =LOOKUP(2,1/((A3:A12="C")*(B3:B12="Y")),C3:C12) -- Biff Microsoft Excel MVP "Billy Leung" wrote in message ... Hello, I've a table below, I would like to setup a query which could return a value showing the 2nd match and last match in the next column. Thank in advance for anyone great help. cheers Billy Column B C D 1st 2nd Row criteria criteria Value 3 A Y 1 4 B Y 2 5 C Y 3 6 A Y 4 7 B X 5 8 C Y 6 9 D X 7 10 A Y 8 11 B Y 9 12 C X 10 query 1 query 2 1st criteria A C 2nd criteria Y Y nth of match 2 last Result 4 6 . . |
Thread Tools | |
Display Modes | |
|
|