My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you

Does that mean the team code appears twice?

A1:A20 = team codes
B1:B20 = team members

D1 = team code to lookup

First team member:

=VLOOKUP(D1,A1:B20,2,0)

Second team member:

=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Biff

"vsoler" wrote in message
ups.com...

My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you

On 21 mayo, 09:32, "T. Valko" wrote:
Does that mean the team code appears twice?

A1:A20 = team codes
B1:B20 = team members

D1 = team code to lookup

First team member:

=VLOOKUP(D1,A1:B20,2,0)

Second team member:

=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Biff

"vsoler" wrote in message

ups.com...





My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

Your formula is great, really excellent, I like it.

However, I have tested it in another worksheet that solves another
problem, and it has a peculiar behaviour.

1. No matter what the first argument of the LOOKUP function is, it
always returns the last appearance of the searched value.

2. If I use 3 instead of 2, it also works in the example given to you,
even though the team is made of only 2 people.

I wish I could understand a little more how your formula works.

Thank you very much indeed.

On 21 mayo, 10:10, vsoler wrote:
On 21 mayo, 09:32, "T. Valko" wrote:





Does that mean the team code appears twice?

A1:A20 = team codes
B1:B20 = team members

D1 = team code to lookup

First team member:

=VLOOKUP(D1,A1:B20,2,0)

Second team member:

=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Biff

"vsoler" wrote in message

oups.com...

My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

Your formula is great, really excellent, I like it.

However, I have tested it in another worksheet that solves another
problem, and it has a peculiar behaviour.

1. No matter what the first argument of the LOOKUP function is, it
always returns the last appearance of the searched value.

2. If I use 3 instead of 2, it also works in the example given to you,
even though the team is made of only 2 people.

I wish I could understand a little more how your formula works.

Thank you very much indeed.- Ocultar texto de la cita -

- Mostrar texto de la cita -

Just another comment:

Range A1:A20 may not be sorted. What happens then?

"vsoler" wrote in message
oups.com...
On 21 mayo, 10:10, vsoler wrote:
On 21 mayo, 09:32, "T. Valko" wrote:





Does that mean the team code appears twice?

A1:A20 = team codes
B1:B20 = team members

D1 = team code to lookup

First team member:

=VLOOKUP(D1,A1:B20,2,0)

Second team member:

=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Biff

"vsoler" wrote in message

oups.com...

My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using
VLOOKUP
or any other function.

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

Your formula is great, really excellent, I like it.

However, I have tested it in another worksheet that solves another
problem, and it has a peculiar behaviour.

1. No matter what the first argument of the LOOKUP function is, it
always returns the last appearance of the searched value.

2. If I use 3 instead of 2, it also works in the example given to you,
even though the team is made of only 2 people.

I wish I could understand a little more how your formula works.

Thank you very much indeed.- Ocultar texto de la cita -

- Mostrar texto de la cita -

Just another comment:

Range A1:A20 may not be sorted. What happens then?


Nothing, it doesn't matter. See my detailed explanation in the other branch
of this thread.

Biff

"vsoler" wrote in message
ups.com...
On 21 mayo, 09:32, "T. Valko" wrote:
Does that mean the team code appears twice?

A1:A20 = team codes
B1:B20 = team members

D1 = team code to lookup

First team member:

=VLOOKUP(D1,A1:B20,2,0)

Second team member:

=LOOKUP(2,1/(A1:A20=D1),B1:B20)

Biff

"vsoler" wrote in message

ups.com...





My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

T. Valko,

Your formula is great, really excellent, I like it.

However, I have tested it in another worksheet that solves another
problem, and it has a peculiar behaviour.

1. No matter what the first argument of the LOOKUP function is, it
always returns the last appearance of the searched value.

2. If I use 3 instead of 2, it also works in the example given to you,
even though the team is made of only 2 people.

I wish I could understand a little more how your formula works.

Thank you very much indeed.


=LOOKUP(2,1/(A1:A20=D1),B1:B20)

The first argument doesn't mean to find the 2nd instance of a particular
value. The formula will find the *LAST* instance of the value be it the 2nd
instance or the 20th instance. The fact that you had 2 instances and the
first argument is also a 2 is just a coincidence.

Here's how it works:

LOOKUP takes these arguments:

LOOKUP(lookup_value,lookup_vector,result_vector)

In our formula the lookup_value is 2

The lookup_vector is 1/(A1:A20=D1)

The result_vector is B1:B20

This means we want to find the value 2 in the lookup_vector and return the
corresponding value from the result_vector.

This portion of the lookup_vector:

(A1:A20=D1)

Will return an array of either TRUE or FALSE.

Dividing those logical values by 1:

1/(A1:A20=D1)

Will result in an array of either 1 or a #DIV/0! error

1/(TRUE) = 1
1/(FALSE) = #DIV/0!

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.

That's where the lookup_value of 2 comes into play. Since our lookup_vector
comprises an array of 1s and #DIV errors the *LAST* value that is less than
the lookup_value has to be a 1.

Let's look at a small sample of data to see how this works:

...........A..........B..........C..........D
1........X........Joe......................X
2........Y........Tom
3........Z........Sue
4........X........Bill

You want to extract the members of team X and there are 2 members. We have
the team to lookup in D1, X.

We can use a simple VLOOKUP formula to get the first member of the team,
Joe. Since VLOOKUP only works for a single instance of a lookup value we
then need to use another formula. The LOOKUP formula is the perfect choice.

=LOOKUP(2,1/(A1:A4=D1),B1:B4)

The lookup_vector is:

1/(A1=D1) = 1/(TRUE) = 1
1/(A2=D1) = 1/(FALSE) = #DIV/0!
1/(A3=D1) = 1/(FALSE) = #DIV/0!
1/(A4=F1) = 1/(TRUE) = 1

This is how that would look applied to the range of cells:

......lookup_vector.....result_vector
1.............1.....................Joe
2.............#DIV..............Tom
3.............#DIV..............Sue
4.............1.....................Bill

Since the lookup_value is 2 and 2 is greater than any value in the
lookup_vector the result of the formula is the value from the result_vector
that corresponds to the *LAST* value from the lookup_vector that is less
than the lookup_value. The *LAST* value from the lookup_vector that is less
than the lookup_value is the 1 in A4. So the result of the formula is the
corresponding value from B4 which is Bill.

Hope that's not confusing!

Biff

a completely different way:-


=INDEX($A$1:$B20,SMALL(IF($A$1:$B20=$D$1,ROW($A$1: $B20)-ROW($A$1)+1,ROW($B20)+1),2),2)

In this formula the lookup value id in D1. The second to last 2 is the
record to look for, change this to a 1 and it will find the third record etc.
It's an array so enter with ctrl+shift+enter

Mike

My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you

oops type change the 2 to a 3 to find the 3rd record

"Mike H" wrote:

a completely different way:-


=INDEX($A$1:$B20,SMALL(IF($A$1:$B20=$D$1,ROW($A$1: $B20)-ROW($A$1)+1,ROW($B20)+1),2),2)

In this formula the lookup value id in D1. The second to last 2 is the
record to look for, change this to a 1 and it will find the third record etc.
It's an array so enter with ctrl+shift+enter

Mike

My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you

On 21 mayo, 11:19, Mike H wrote:
oops type change the 2 to a 3 to find the 3rd record



"Mike H" wrote:
a completely different way:-

=INDEX($A$1:$B20,SMALL(IF($A$1:$B20=$D$1,ROW($A$1: $B20)-ROW($A$1)+1,ROW($B2*0)+1),2),2)

In this formula the lookup value id in D1. The second to last 2 is the
record to look for, change this to a 1 and it will find the third record etc.
It's an array so enter with ctrl+shift+enter

Mike

My table (range) contains, among other information, the code for a
team and the name of the person. Each team is made of 2 people.

I need to find the second person belonging to a team, which will not
be necessarily just below the first person.

Is there a way to find this second person. I do not mind using VLOOKUP
or any other function.

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

Mike,

Really great, it works fine.

Thanks a lot.