# Approximate Index Match With Multiple Criteria

#### JetEscamilla

##### Board Regular
Hello, I have a need to pull approximate values by using an index/match type array function with multiple variables. I have output with the following column headings

Case Type - Min or Max
M3 - Number (this is what I want for output)
Girder # - Numbers 1 to 13 (These will be output in clusters and in order)
Girder Location (ft) - Number (again, will be in numerical order but with the girders, for example when girder is 2 these rows will start at 0 and work their way up, then girder 3, etc...)

My goal is to use the variables of Case, Girder, and Location to pull the approximate M3 force value closest to the location.

I've tried {Index(D3:D22, Match(Location,(CaseType=A3:A22)*(Girder=B3:B22)*(C3:C22),1)}

With Location = 12, CaseType = Max, Girder = 2 I should return 80 for my force. However this formula is always returning the last row of my index (row 22). I believe this is because the array that is made by my multiplication has zeros at the end. The match case I showed above would give {0,0,0,0,0,0,0,4,8,16,20,0,0,0,0,0,0,0,0}. Does index not work with trailing zeros in the array? My locations may not be exact matches, instead I always want to pull the row that is lower, never above.

Does anyone have a suggestion for another work around?

 A B C D 1 CaseType Girder Location M3 2 Text Kip-ft Kip-ft 3 Max 1 0 0 4 Max 1 4 33 5 Max 1 8 52 6 Max 1 12 48 7 Max 1 16 15 8 Max 1 20 0 9 Max 2 0 0 10 Max 2 4 40 11 Max 2 8 80 12 Max 2 16 74 13 Max 2 20 35 14 Max 2 24 0 15 Max 3 0 0 16 Max 3 4 14 17 Max 3 8 28 18 Max 3 12 17 19 Max 3 16 0 20 Min 1 0 0 21 Min 1 4 -25 22 Min 1 8 0​

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

##### Well-known Member
Hi JetEscamilla,

Does this do what you want?

JetEscamilla.xlsx
ABCDEFGHI
1CaseTypeGirderLocationM3LocationCase TypeGirderResult
2TextKip-ftKip-ft12Max280
3Max100
4Max1433
5Max1852
6Max11248
7Max11615
8Max1200
9Max200
10Max2440
11Max2880
12Max21674
13Max22035
14Max2240
15Max300
16Max3414
17Max3828
18Max31217
19Max3160
20Min100
21Min14-25
22Min180
Sheet1
Cell Formulas
RangeFormula
I2I2=IFERROR(INDEX(\$D\$3:\$D\$22,AGGREGATE(14,6,ROW(\$C\$3:\$C\$22)-ROW(\$C\$2)/((\$A\$3:\$A\$22=\$G\$2)*(\$B\$3:\$B\$22=\$H\$2)*(\$C\$3:\$C\$22<=\$F\$2)),1)),"No match")

#### Peter_SSs

##### MrExcel MVP, Moderator
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Given the above,
IF
you have MS365 then you could do it like I have done in G5.
For other versions you could try the G6 formula, confirmed with Ctrl+Shift+Enter, not just Enter.

21 12 02.xlsm
ABCDEFG
1CaseTypeGirderLocationM3
2TextKip-ftKip-ftLocation12
3Max100CaseMax
4Max1433Girder2
5Max1852M380
6Max1124880
7Max11615
8Max1200
9Max200
10Max2440
11Max2880
12Max21674
13Max22035
14Max2240
15Max300
16Max3414
17Max3828
18Max31217
19Max3160
20Min100
21Min14-25
22Min180
Lookup
Cell Formulas
RangeFormula
G5G5=VLOOKUP(G2,FILTER(C3:D22,(A3:A22=G3)*(B3:B22=G4)),2)
G6G6=VLOOKUP(G2,IF(A3:A22=G3,IF(B3:B22=G4,C3:D22)),2)
Press CTRL+SHIFT+ENTER to enter array formulas.

#### JetEscamilla

##### Board Regular
Thank you both for the help and for the recommendation to update my profile settings. It's been a while since I had to post here (a good thing I guess) and did not know that was an option. Again, appreciate the help!

You're welcome.

Replies
7
Views
198
Replies
8
Views
226
Replies
5
Views
252
Replies
1
Views
91
Replies
1
Views
196

1,196,016
Messages
6,012,865
Members
441,737
Latest member
bijayche

### 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.

### Which adblocker are you using?

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

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