LOOKUP DIFFERENTIATION WHERE HAVE SAME NAME IN TABLE

[rebelhunter]

A B
NAME AGE

1 dan 10
2 john 15
3 shaun 18
4 dan 27
5 craig 35
6 dan 47

How can i use Vlookup to automatically find the figure in column b, row 4 everytime, within the above table, when the position of dan could change, if more names are added, but the above names stay constant and the figures change??

Regards,
Shaun

 

[yosi]

use index function with match nested in

=index(a1:c50,match(e1,b:b,0),3))

the data table - a1:c50
in cell e1 - enter any name to search
This message was edited by yosi on 2002-10-25 07:19
This message was edited by yosi on 2002-10-25 11:34

 

[Aladin Akyurek]

On 2002-10-25 07:01, rebelhunter wrote:
A B
NAME AGE

1 dan 10
2 john 15
3 shaun 18
4 dan 27
5 craig 35
6 dan 47

How can i use Vlookup to automatically find the figure in column b, row 4 everytime, within the above table, when the position of dan could change, if more names are added, but the above names stay constant and the figures change??

Regards,
Shaun

How can 'dan' have 3 different ages?

 

[DonkeyOte]

different dan's perhaps?

 

[rebelhunter]

This is an example just to show the logic i am looking for. Basically i want to know how i can direct Vlookup to find figures with the same description next to them, by finding the 1st figure and then direct the function to the next figure with the same description and find the figure next to that??

 

[yosi]

Aladin mention that Dan has 3 ages
the match formula with 0 at the third argument will find the first one,
if you sort the list by name ( column B ) enter in the third argumant 1 and get the last one.