Lookup 2nd & last match with two conditions

[Billy Leung]

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

 

[T. Valko]

For the 2nd instance...

Array entered** :

=INDEX(C:C,SMALL(IF(A3:A12="A",IF(B3:B12="Y",ROW(C3: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)

 

[Billy Leung]

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]

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

 

[T. Valko]

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!

 

[Billy Leung]

T. Valko,

Thanks a lot, I really learnt from your illistration.

 

[T. Valko]

You're welcome. Thanks for the feedback!