INDEX MATCH only returning first match

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
128
Office Version
  1. 2021
Platform
  1. Windows
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))

Book1 .png
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi Stuepef,

If your version supports the FILTER function, you could try this formula in K4, K12, and K20:
Excel Formula:
=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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
I thought of another way that I learned recently. You can use MATCH with multiple criteria:oops:

Book1
ABCDEFGHIJKLM
14abcAIAdef
15abcAILIA
16abcAILA
17defAIL502005006returns sixth row in range
18defAIAexpand with index to return data
19defAIA100200300
20ghiAMN
21ghiAMN222333444
Sheet1
Cell Formulas
RangeFormula
I17I17=MATCH(I14&I15&I16&TRUE,A14:A21&C14:C21&B14:B21&NOT(ISBLANK(D14:D21)),0)


Enjoy!

Doug
 
Upvote 1
Solution
I thought of another way that I learned recently. You can use MATCH with multiple criteria:oops:

Book1
ABCDEFGHIJKLM
14abcAIAdef
15abcAILIA
16abcAILA
17defAIL502005006returns sixth row in range
18defAIAexpand with index to return data
19defAIA100200300
20ghiAMN
21ghiAMN222333444
Sheet1
Cell Formulas
RangeFormula
I17I17=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!!
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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