vlookup type problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ciaran.hudson@gmail.com - 04 Mar 2008 18:14 GMT

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

Reply to this Message

myemail.an@googlemail.com - 04 Mar 2008 18:24 GMT

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?

Reply to this Message

Pete_UK - 05 Mar 2008 00:44 GMT

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

On Mar 4, 6:14 pm, ciaran.hud...@gmail.com wrote:

> I've a problem that a standard vlookup will not solve for me as it
> always returns the first value it finds.
[quoted text clipped - 37 lines]
> Regards,
> Ciarán

Reply to this Message

Pete_UK - 05 Mar 2008 00:46 GMT

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

> 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:
[quoted text clipped - 64 lines]
>
> - Show quoted text -

Reply to this Message

Rate this thread: