Approximate Index Match With Multiple Criteria

[JetEscamilla]

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​

 

[Toadstool]

Hi JetEscamilla,

Does this do what you want?

JetEscamilla.xlsx
	A	B	C	D	E	F	G	H	I
1	CaseType	Girder	Location	M3		Location	Case Type	Girder	Result
2	Text		Kip-ft	Kip-ft		12	Max	2	80
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
Sheet1

Cell Formulas
Range	Formula
I2	I2	=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]

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
	A	B	C	D	E	F	G
1	CaseType	Girder	Location	M3
2	Text		Kip-ft	Kip-ft		Location	12
3	Max	1	0	0		Case	Max
4	Max	1	4	33		Girder	2
5	Max	1	8	52		M3	80
6	Max	1	12	48			80
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
Lookup

Cell Formulas
Range	Formula
G5	G5	=VLOOKUP(G2,FILTER(C3:D22,(A3:A22=G3)*(B3:B22=G4)),2)
G6	G6	=VLOOKUP(G2,IF(A3:A22=G3,IF(B3:B22=G4,C3:D22)),2)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JetEscamilla]

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!

 

[Toadstool]

You're welcome.