# Alternate to Index+Match functions with duplicate values

#### FALGESH

##### New Member
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.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?

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

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

Thanks a Lot, Friends

1,203,542
Messages
6,056,012
Members
444,840
Latest member
RazzelDazel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back