INDEX MATCH only returning first match

[Stuepef]

I have a spreadsheet that has over 100 companies and their associated plans, locations and rating tiers (1-4). The user would manually select the company, plan and location and the formula would retrieve the 4 different rates. I tried using INDEX MATCH, but the problem is if the rates are not in the very first match, it retrieves $0.00 instead of the actual rate. My thought is to use AGGREGATE instead of match, but I am unsure how to create the formula.

Rate 1 Formula =INDEX($D$2:$D$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))

Rate 2 Formula =INDEX($E$2:$E$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))

Rate 3 Formula =INDEX($F$2:$F$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))

Rate 4 Formula =INDEX($G$2:$G$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))

 

[duggie33]

I thought of another way that I learned recently. You can use MATCH with multiple criteria

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
14	abc	A	IA						def
15	abc	A	IL						IA
16	abc	A	IL						A
17	def	A	IL	50	200	500			6	returns sixth row in range
18	def	A	IA							expand with index to return data
19	def	A	IA	100	200	300
20	ghi	A	MN
21	ghi	A	MN	222	333	444
Sheet1

Cell Formulas
Range	Formula
I17	I17	=MATCH(I14&I15&I16&TRUE,A14:A21&C14:C21&B14:B21&NOT(ISBLANK(D14:D21)),0)

Enjoy!

Doug

 

[duggie33]

Hi Stuepef,

If your version supports the FILTER function, you could try this formula in K4, K12, and K20:

=TRANSPOSE(FILTER($D$2:$G$23,($A$2:$A$23=N1)*($B$2:$B$23=N2)*($C$2:$C$23=N3)*($D$2:$D$23<>""),"error"))

In row 19, "Plan 2" is missing the space.

Hope that helps,

Doug

 

[Stuepef]

I wish I could use the filter function, but due to users have various versions of excel when using the spreadsheet, I am not sure the Filter function would work.

 

[duggie33]

You could probably use the Advanced Filter on the Data tab. It can be tricky, but there are a lot of resources on how to use it.

 

[Stuepef]

I thought of another way that I learned recently. You can use MATCH with multiple criteria

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
14	abc	A	IA						def
15	abc	A	IL						IA
16	abc	A	IL						A
17	def	A	IL	50	200	500			6	returns sixth row in range
18	def	A	IA							expand with index to return data
19	def	A	IA	100	200	300
20	ghi	A	MN
21	ghi	A	MN	222	333	444
Sheet1

Cell Formulas
Range	Formula
I17	I17	=MATCH(I14&I15&I16&TRUE,A14:A21&C14:C21&B14:B21&NOT(ISBLANK(D14:D21)),0)

Enjoy!

Doug

Works like a charm and learn something new each day - THANK YOU!!