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

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

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



.

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



.

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



.

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



.





.

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



.





.