Find a second value in a table with VLOOKUP

[vsoler]

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

 

[T. Valko]

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]

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











- 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.

 

[vsoler]

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?

 

[Mike H]

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

 

[Mike H]

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

 

[vsoler]

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





- Mostrar texto de la cita -

Mike,

Really great, it works fine.

Thanks a lot.

 

[T. Valko]

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

 

[T. Valko]

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