INDEX MATCH formula with criteria

[cjcass]

Hi,
I'm looking for a formula please (not vba) to enter into cell E15 that will reference the criteria entered into cells E12 & E13 and also the operators in cells B4 & C4 and give the result from the table. Note, the operators in cells B4 & C4 may change hence the need for them to be referenced.
Any help much appreciated.

 

[jasonb75]

Based on the screen capture

=INDEX(D5:F7,MATCH(E12,B5:B7,-1),MATCH(E12,D4:F4,0))

 

[queuesmef]

in cell E15 place the following formula:

=INDEX(D4:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))

investigate index and match

 

[cjcass]

Based on the screen capture

=INDEX(D5:F7,MATCH(E12,B5:B7,-1),MATCH(E12,D4:F4,0))

Hi,
Thanks for your time but this formula isn't working, it also doesn't reference column C

 

[cjcass]

in cell E15 place the following formula:

=INDEX(D4:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))

investigate index and match

Hi, thanks but this returns 270 instead of 350 and doesn't reference Col B

 

[Fluff]

Thanks for your time but this formula isn't working

In what way?

 

[jasonb75]

Hi,
Thanks for your time but this formula isn't working, it also doesn't reference column C

Typo in the formula, it happens sometimes when you post screen captures instead of usable (XL2BB) examples. Also it doesn't need to reference column C, matching to B is adequate based on the example provided.

Book1
	B	C	D	E	F
4	<	>=	USA	Germany	France
5	10	7	200	270	67
6	7	5	300	350	87
7	5	4	400	430	107
8	4	0	500	510	127
9
10
11
12				6
13				Germany
14
15				350
Sheet1

Cell Formulas
Range	Formula
E15	E15	=INDEX(D5:F8,MATCH(E12,B5:B8,-1),MATCH(E13,D4:F4,0))

 

[cjcass]

Hi, thanks but this returns 270 instead of 350 and doesn't reference Col B

ok, spotted a slight issue and made the change to... =INDEX(D5:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))
This works on one operator column only and is workable if the Col B & Col C entries and cleanly sequential, but if they are not is there a formula that will consider both Cols..?

 

[cjcass]

ok, spotted a slight issue and made the change to... =INDEX(D5:F8,MATCH(E12,C4:C8,-1),MATCH(E13,D4:F4,-1))
This works on one operator column only and is workable if the Col B & Col C entries and cleanly sequential, but if they are not is there a formula that will consider both Cols..?

Please ignore my last comment, that request is overkill.
Thank you all for your input and the solution, much appreciated.

 

[hajiali]

try :

Book1
	A	B	C	D	E	F
1
2
3
4				USA	Germany	France
5		10	7	200	270	67
6		7	5	300	350	87
7		5	4	400	4430	107
8		4	0	500	510	127
9
10
11					CRITERIA
12				NUMBER	6
13				COUNTRY	Germany
14
15			Formula Resuts:		350
Sheet1

Cell Formulas
Range	Formula
E15	E15	=INDEX($D$5:$F$8,AGGREGATE(15,6,(ROW($D$5:$F$8)-ROW($D$5)+1/(($B$5:$B$8<$E$12)+($C$5:$C$8<=$E$12))),ROWS($B$5:B5)),MATCH(E13,D4:F4,0))