Vlookup query - Biff

  • Thread starter Thread starter Lise
  • Start date Start date
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
=IF(COUNTIF(Tasks!B$2:B$576,A59),INDEX(Tasks!A$2:A$576,MATCH(A59,Tasks!B$2:B$576,0)),"-")

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
 
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.
 
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?
 
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
 
Hi Biff

Sorry for late reply - I can actually sort to be either way so which ever
works best with your idea.
 
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

D2 = your lookup value

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