look up a value and return multiple corresponding values.

[gaurav_jalan]

hello,

..................A...............B
1..............tom..............5
2..............jerry.............7
3..............tom..............6
4..............tom..............8
5..............peter............9

in the above example,
"tom" has occured thrice in A column as A1, A3, and A4.

with first occurance remains A1
second occurance remains A3
and third occurance remains A4.

now, is it possible if i want to return with the value of a specific occurance.

i want that the C and D column should look like this :

..................c................d
1..............tom..............5
2..............tom..............6
3..............tom..............8

in this case formula should be inserted in D column.
formula should be inserted in :
D1 to retrieve with the first occurance of Tom.
D2 to retrieve with the second occurance of Tom
D3 to retrieve with the third occurance of Tom.

i want to know which formula can be used for the same...

i found the solution for the same at
http://office.microsoft.com/en-us/assistance/HA012260381033.aspx

its working perfectly.
but the only problem is that, being an Array Formula...it has made my file very complicated.

as i have more then 2000 rows in a column

so whenever i refresh the screen, it takes atleast 30-60 seconds to "calculate Cells"

so i wanted a solution which makes calculations easy...
and solves the problem too.

if i was unable to explain my query , then pls log on
http://office.microsoft.com/en-us/assistance/HA012260381033.aspx

this is what exactly i want but with an easy formula.
that doesnt make it hard on processor...

Thanks

gauravjalan.com

 

[Von Pookie]

Please continue in your original thread:
http://www.mrexcel.com/board2/viewtopic.php?t=176318