Index Match with duplicates and blank cells in index column

Grunter31

Board Regular
Joined
Mar 11, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
In Column S I have a list that I need to match to Column L and get a Price from Column N.

Column L and N always has blank cells between data.

The problem I face is no matter if I use Vlookup, or Index/Match using min function if there is a duplicate match it will always return the first found match.

As you can see from the poorly designed example below I need to match the third list from the first list to get the price in the second list. There are duplicates in both "Team" lists and always random blank cells in first list.

Team A
1.85​
Team A
Team D
Team B
Team B
Team B
2​
Team C
Team A
Team D
Team B
1.5​
Team E
Team C
1.6​
Team D
1.4​
Team E
3.5​
Team A
4.5​
Team D
1.25​
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
So what are you actually trying to achieve? It seems your "required" list (column S?) of teams to get the price is the same number as column L?
 
Upvote 0
Hi Grunter31,

I'm not sure if I'm following and you may have a better solution with FILTER but with Excel 2016 my suggestion is:

Grunter31.xlsx
LMNOST
1Team A1.85Team A1.85
2Team D1.4
3Team B2
4Team B1.5
5Team B2Team C1.6
6Team A4.5
7Team D1.25
8Team B1.5Team E3.5
9
10Team C1.6
11
12Team D1.4
13
14
15Team E3.5
16
17Team A4.5
Sheet1
Cell Formulas
RangeFormula
T1:T8T1=INDEX($N$1:$N$99999,AGGREGATE(15,6,ROW($L$1:$L$99999)/($L$1:$L$99999=S1),COUNTIF($S$1:$S1,S1)))
 
Upvote 0
Hi Grunter31,

I'm not sure if I'm following and you may have a better solution with FILTER but with Excel 2016 my suggestion is:

What you have provided is 100% perfect and works how it should. I never thought to try Aggregate.

Just an extra question if you remove the First Team A in Column L (or any duplicate in Column L) but all Teams in Column S stay the same, is there anyway to leave the first Team A Blank in Column S but still have 4.5 match the the second Team A.



Thank you
 
Upvote 0
I get all the teams listed in Column S with a variation of =IFERROR(INDEX($L$1:$L$200,SMALL(IF($L$1:$L$200<>"",ROW($L$1:$L$200)-ROW($L$1)+1),ROWS($S$1:S2))),"") as an array
 
Upvote 0
If you use that SMALL function then there wouldn't be a missing first Team A, but if there was then you can change the direction of the COUNTIF like below, but this also reverses the direction of the other Teams, e.g. Team B becomes 1.5 then 2 instead of 2 then 1.5.

Grunter31.xlsx
LMNOST
1Team A 
2Team D1.25
3Team B1.5
4Team B2Team B2
5Team C1.6
6Team A4.5
7Team B1.5Team D1.4
8Team E3.5
9Team C1.6
10
11Team D1.4
12
13
14Team E3.5
15
16Team A4.5
17
18
19
20Team D1.25
Sheet1 (2)
Cell Formulas
RangeFormula
T1:T8T1=IFERROR(INDEX($N$1:$N$200,AGGREGATE(15,6,ROW($L$1:$L$200)/($L$1:$L$200=S1),COUNTIF($S1:$S$200,S1))),"")
 
Upvote 0
Solution

Toadstool

Thanks you so much for your time both ideas will work but like everything will fall over in an extreme situation. I think I will use 2 columns. Then I think I can use ""if cell = blank then"

I would never have got to this stage without your help. Thanks again
 
Upvote 0

Toadstool

Thanks you so much for your time both ideas will work but like everything will fall over in an extreme situation. I think I will use 2 columns. Then I think I can use ""if cell = blank then"

I would never have got to this stage without your help. Thanks again
You're welcome!
Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,900
Messages
6,181,635
Members
453,059
Latest member
jkevin

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
Back
Top