# Vlookup query - Biff

L

#### Lise

Hi Biff - sorry your #1 Pain again!! I did put this in a thread but don't
think it went through.

all is working well (+ I've made some changes based on other tips from you
on previous notes) however there are still issues with duplicates.

Currently I have as an exampl

On the tasks sheet row 59,60 and 61 column B are all the same which is the
same as the number listed on the current sheet in A59, A60 & A61 - but the
data I'm collecting from the tasks sheet in columns D, F & J (same rows)
change on each row which the formula isnt picking up

The formula gives the correct answer for A59 but when I drag down to use for
A60 and A61 it only provides the row 59 answers again.

Gosh I hope this makes it clearer Biff its the only way I can think to
clarify for you.

Any assistance as always appreciated

Lise

T

#### T. Valko

I'm not sure I understand.

This is what it sounds like to me...

Your lookup value in A59 has more than one instance in Tasks!B\$2:B\$576 so
the formula is returning the same result from Tasks!A\$2:A\$576 for each
instance of the lookup value. For eample:

A59 = 1

1...first
1...second
1...third

Every time you lookup 1 the result is first.

L

#### Lise

Yes that's right - so the 1 (using your example) shows on 3 seperate rows in
the sheet I want the answers to go to but the data in the others columns
(which is different each time) does not it only keeps repeating the first
data.

am I asking too much do you think?

T

#### T. Valko

am I asking too much do you think?

Not yet! <g>

The best way to do this defpends on how your data is setup.

Is the data to lookup sorted or grouped together like this:

1...first
1...second
1...third
2...first
3...first
3...second

Or is it random:

1...first
2...first
1...second
3...first
1...third
3...second

L

#### Lise

Hi Biff

Sorry for late reply - I can actually sort to be either way so which ever

T

#### T. Valko

If the data is sorted then the formula is less complicated!

Let's assume this is your data sorted by column A:

...........A..........B
1.......................
2........1..........A
3........1..........C
4........1..........E
5........2..........K
6........3..........L
7........3..........P

Enter this formula in E2 and copy down until you get blanks:

=IF(ROWS(E\$2:E2)>COUNTIF(A\$2:A\$7,D\$2),"",INDEX(B\$2:B\$7,MATCH(D\$2,A\$2:A\$7,0)+ROWS(E\$2:E2)-1))

You need to copy to a number of cells that is at least equal to the max
number of instances of any one lookup value. For example, in the above 1
appears the most times, 3. So, you need to copy the formula to at least 3
cells.

L

#### Lise

Fantastic as always Biff - works like a charm