Alternate to Index+Match functions with duplicate values

[FALGESH]

I'm trying to pull data frm one sheet (~16000 rows) to another sheet, making use of Index and Match function from a cell (WHREIN i'm making use of function LARGE/SMALL).

The problem is, where a cell content is identical in two or more cells (where LARGE/SMALL function is used), the Index + Match Functions returns the first value that it finds in the range.

Is there any otherway round whereby, i can pull all the relevant data

Illustration

A B C D E
1 ABC DEF 36 GEF HIJ
2 PQR WZX 54 POL MNB
3 AQW WER 36 TYU GFR
4 CDF VFG 48 NJM MKL
5 CDS XSD 40 GHJ UIO
6 ZXC VBN 36 BHU OKM

As can be seen frm above, in Column C, value of 36 is being repeated on 3 occassions.

Now when i try to pull corresponding data from coulmn A/B/D or E, making use of Index+Match function, it returns value of C1 only even against C3, C6.

Thanks in advance.

 

[Yard]

Hi and welcome,

What result do you want to get, e.g. you want ABC, AQW and ZXC to appear in 3 cells? And what is your current INDEX formula?

 

[FALGESH]

I want each of them in seperate cells.

My base Cell Formula (where i get the Max value from another sheet, which but natural is in ascending order in the result sheet)

The Formula for Base Cell is something like
=LARGE(Pull!K:K,ROW()-1)


Then i search for value depending on Base Cell content (where Max value is obtained, which in few cases have identical numbers) using Index+Match Function


Then from above result i try to locate corresponding value in different columns using following formula

The Formula for Base Cell is something like

=INDEX(Pull!I:I,MATCH(F2,Pull!K:K,0))

The issue is that in Sheet (Pull), few of the cells in Column (K) has identical (duplicate) values which makes the subsequent formula to retrieve the value from cell that is first encountered, resulting in SAME CORRESPONDING VALUE for each 36, instead of corresponding value.

Trust, explained properly.......

 

[c_m]

See Aladin's post here:
http://www.mrexcel.com/forum/showthread.php?p=1876716#post1876716

your problem is about returning multiple matching values....try to search this forum for that

 

[FALGESH]

Ya, Getting some clue, but in midst of something, will try in the evening,

Thanks a Lot, Friends