Alternate to Index+Match functions with duplicate values

FALGESH

New Member
Joined
Mar 18, 2009
Messages
3
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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

New Member
Joined
Mar 18, 2009
Messages
3
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:

FALGESH

New Member
Joined
Mar 18, 2009
Messages
3
Ya, Getting some clue, but in midst of something, will try in the evening,

Thanks a Lot, Friends
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top