Two INDEX conditions to be checked with 'IF' condition

[LesliePrabakar]

Greetings to all.

How to check/combine the below 2 conditions with 'IF' condition?

=INDEX(***,MATCH(***,***)) > A1 AND
=INDEX(***,MATCH(***,***)) > B1

Thanks in advance

[Glenn Kennedy]

Like this:

=INDEX(TargetRange,MATCH(1,(Criterion1=CriterionRange1)*(Criterion2=CriterionRange2),0))

entered as an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[LesliePrabakar]

Like this:

=INDEX(TargetRange,MATCH(1,(Criterion1=CriterionRange1)*(Criterion2=CriterionRange2),0))

entered as an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

This is the formula:
=INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2)) > IG2 AND =INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2)) > IG2

Can I get in your format Glenn, please..

[Glenn Kennedy]

What, exactly, are you trying to do?

[LesliePrabakar]

What, exactly, are you trying to do?

=INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2)) returns X (for instance: 50.72)

X needs to be greater than A (for instance: 50) and B (for instance: 40) cells' values.

I am not much sure about how to do with the formula written by you; can you please help..

[LesliePrabakar]

Am trying to compare a cell's value with other two cells (by 'IF' condition) for the written INDEX and MATCH formula..

[Glenn Kennedy]

=INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2)) returns the last numerical value in the range B2-IA2. So, if the returned value is greater than value X and value Y... what do you want the result to be?????

[LesliePrabakar]

=INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2)) returns the last numerical value in the range B2-IA2. So, if the returned value is greater than value X and value Y... what do you want the result to be?????

Result has to be PASS else FAIL, Glenn.

[Glenn Kennedy]

Try this, then.

=if(and(INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2))>A1,INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2))>b1),"Pass","Fail")

[Tony Valko]

=INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2)) returns X (for instance: 50.72)

X needs to be greater than A (for instance: 50) and B (for instance: 40) cells' values.

In other words X needs to be greater than the maximum of either 50 or 40?

Maybe something like this...

Data Range

	B	C	D	E	F	G	H	I	J
1
2	4	11	13	18	75	70	72	81	41
3
4	50	40	81
5

=LOOKUP(2,1/(B2:J2>MAX(B4:C4)),B2:J2)

[LesliePrabakar]

Try this, then.

=if(and(INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2))>A1,INDEX(B2:IA2,MATCH(9.9999999E+307,B2:IA2))>b1),"Pass","Fail")

Awesome, Glenn. Perfectly works for me.

You are appreciated well for your time spent on this query and patience in understanding.

Take good care.

[LesliePrabakar]

In other words X needs to be greater than the maximum of either 50 or 40?

Maybe something like this...

Data Range

	B	C	D	E	F	G	H	I	J
1
2	4	11	13	18	75	70	72	81	41
3
4	50	40	81
5

=LOOKUP(2,1/(B2:J2>MAX(B4:C4)),B2:J2)

Thanks so much Tony for the reply.

[Glenn Kennedy]

Glad to have helped. Sorry it took a while, though....

[Tony Valko]

I may not have understood the requirement.

[Glenn Kennedy]

That's not like you, T. You're usually bang on target...