vlookup type problem

[ciaran.hudson]

I've a problem that a standard vlookup will not solve for me as it
always returns the first value it finds.

My problem can be illustrated by the fictional example below.

I have 2 tabs in a workbook; tab1 and tab2.

Tab1 has one populated column and the heading of it is name.
Tab2 has two populated columns and the headings are name and age.

Tab1 contains the following data.

Name
John
Mark
Luke
Luke

Tab2 contains the following data.
Name Age
John 23
Mark 22
Luke 26
Luke 27

The two Lukes are different people.

If I use a formula like below on Tab1, Luke will have the age 26 twice
and the age 27 will not be picked up.
=VLOOKUP(A2,Tab2!$A$2:$B$5,2,0)



Is there anyway to edit the vlookup formula to solve my problem?

I was trying to make the vlookup formula know the row number it found
"Luke" at the first time and edit it's range to begin looking for
"Luke" the second time at the next row, but to no avail.

All help would be gratefully received.

Regards,
Ciarán

 

[myemail.an]

Sounds to me like a problem that should be analyzed with a database
and not a spreadsheet. Are you familiar with SQL, or at least with
Access?

 

[Pete_UK]

You need to have unique identifiers. One way of doing it is to insert
a new column A in Tab2 and put this formula in A2:

=IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))

Copy this down and you will get:

Ref Name Age
John_1 John 23
Mark_1 Mark 22
Luke_1 Luke 26
Luke_2 Luke 27

Then your lookup formula becomes:

=VLOOKUP(A2&"_"&COUNTIF(A$2:A2,A2),Tab2!$A$2:$B$5,2,0)

copy this down as required.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, you need to take account of the extra column - the formula
would be:

=VLOOKUP(A2&"_"&COUNTIF(A$2:A2,A2),Tab2!$A$2:$C$5,3,0)

Hope this helps.

Pete