INDEX & MATCH using 3 conditions with 'in between'

[devo1234]

Hi,

{=INDEX(K243:K274,MATCH(O245&P245,G243:G274&H243:H274))}

Tried to insert an image but it's not working.. please see below for screenshots..
https://imgur.com/a/CUyDEm0
https://imgur.com/5TOZpLs

The table on the left.. has 4 components, portfolio, risk, term (from and to) and value. The table on the right is my attempt to capture the right value. Column value on the right is what table on the left has and my index formula is not picking up the right value since column term on the right is an integer whereas table left has term from & to.

How do I insert a condition that would allow my formula to search for a value in between such as 2,3 or 7? If from and to are the same i.e. 1 then I can work around this manually for now

Thanks

 

[sheetspread]

Does this work?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Portfolio	Risk	TermFrom	TermTo	Value			Portfolio	Risk	Term	Value
2	HGB	A0	0	1	1			HGB	B1	1	2
3	HGB	A1	0	1	2			HGB	A3	2	3
4	HGB	A2	0	1	2
5	HGB	A3	0	1	2
6	HGB	B1	0	1	2
7	HGB	B2	0	1	2
8	HGB	B3	0	1	2
9	HGB	C1	0	1	2
10	HGB	A0	1	5	2
11	HGB	A1	1	5	3
12	HGB	A2	1	5	3
13	HGB	A3	1	5	3
14	HGB	B1	1	5	3
15	HGB	B2	1	5	3
16	HGB	B3	1	5	3
17	HGB	C1	1	5	3
18	HGB	A0	5	10	2
19	HGB	A1	5	10	4
20	HGB	A2	5	10	4
21	HGB	A3	5	10	4
22	HGB	B1	5	10	4
23	HGB	B2	5	10	4
24	HGB	B3	5	10	4
25	HGB	C1	5	10	4
26	HGB	A0	10	99	2
27	HGB	A1	10	99	4
28	HGB	A2	10	99	4
29	HGB	A3	10	99	4
30	HGB	B1	10	99	4
31	HGB	B2	10	99	4
32	HGB	B3	10	99	4
33	HGB	C1	10	99	4
Sheet9

Cell Formulas
Range	Formula
K2	=SUMIFS($E$2:$E$33,$A$2:$A$33,H2,$B$2:$B$33,I2,$C$2:$C$33,"<"&J2,$D$2:$D$33,">="&J2)

 

[devo1234]

Nicely done! Thank you very much!

Could you please confirm the logic you used can be stated as follow:

For criteria 3, if my term is 9 (column J) look for any value below column C THEN for any value above or equal term 9 (column J) look for any value above or equal TermTo (column D)

Did not think in using a SUMIFS...

Hats to you...

 

[sheetspread]

please confirm the logic you used can be stated as follow:

For criteria 3, if my term is 9 (column J) look for any value below column C THEN for any value above or equal term 9 (column J) look for any value above or equal TermTo (column D)

Yes that's right

Nicely done! Thank you very much! Hats to you...