Vlookup query - Biff
Fantastic as always Biff - works like a charm
--
Thanks so much
Lise
"T. Valko" wrote:
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.
--
Biff
Microsoft Excel MVP
"Lise" wrote in message
...
Hi Biff
Sorry for late reply - I can actually sort to be either way so which ever
works best with your idea.
--
Thanks as always
Lise
"T. Valko" wrote:
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
--
Biff
Microsoft Excel MVP
"Lise" wrote in message
...
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?
--
Thanks as always
Lise
"T. Valko" wrote:
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.
--
Biff
Microsoft Excel MVP
"Lise" wrote in message
...
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 example
=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
--
Thanks as always
Lise
.
.
.
|