Index Match with duplicates and blank cells in index column

[Grunter31]

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​

 

[aRandomHelper]

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?

 

[Toadstool]

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
	L	M	N	O				S	T
1	Team A		1.85					Team A	1.85
2								Team D	1.4
3								Team B	2
4								Team B	1.5
5	Team B		2					Team C	1.6
6								Team A	4.5
7								Team D	1.25
8	Team B		1.5					Team E	3.5
9
10	Team C		1.6
11
12	Team D		1.4
13
14
15	Team E		3.5
16
17	Team A		4.5
Sheet1

Cell Formulas
Range	Formula
T1:T8	T1	=INDEX($N$1:$N$99999,AGGREGATE(15,6,ROW($L$1:$L$99999)/($L$1:$L$99999=S1),COUNTIF($S$1:$S1,S1)))

 

[Grunter31]

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

 

[Grunter31]

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

 

[Toadstool]

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
	L	M	N	O				S	T
1								Team A
2								Team D	1.25
3								Team B	1.5
4	Team B		2					Team B	2
5								Team C	1.6
6								Team A	4.5
7	Team B		1.5					Team D	1.4
8								Team E	3.5
9	Team C		1.6
10
11	Team D		1.4
12
13
14	Team E		3.5
15
16	Team A		4.5
17
18
19
20	Team D		1.25
Sheet1 (2)

Cell Formulas
Range	Formula
T1:T8	T1	=IFERROR(INDEX($N$1:$N$200,AGGREGATE(15,6,ROW($L$1:$L$200)/($L$1:$L$200=S1),COUNTIF($S1:$S$200,S1))),"")

 

[Grunter31]

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

 

[Toadstool]

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.