A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup 2nd & last match with two conditions



 
 
Thread Tools Display Modes
  #1  
Old March 19th, 2010, 03:40 AM posted to microsoft.public.excel.worksheet.functions
Billy Leung[_2_]
external usenet poster
 
Posts: 6
Default 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  
Old March 19th, 2010, 03:58 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old March 19th, 2010, 05:14 AM posted to microsoft.public.excel.worksheet.functions
Billy Leung[_2_]
external usenet poster
 
Posts: 6
Default 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  
Old March 19th, 2010, 06:13 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old March 19th, 2010, 06:24 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old March 19th, 2010, 07:25 AM posted to microsoft.public.excel.worksheet.functions
Billy Leung[_2_]
external usenet poster
 
Posts: 6
Default 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  
Old March 19th, 2010, 06:34 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.