LOOKUP Function Replacement

Try this...

=INDEX(A:A,INDEX(MAX((C38:C92>=I10)*(E38:E92>=I10)*ROW(A38:A92)),0))

Thank you,
The formula given works fine, however when applying the same given formula to a different cell formula contains the LOOKUP formula below after changing some values it did not works with me!!

Would you please edit the following formulas in the same way as you did with the first one.

INDEX is A38:G92

=LOOKUP(M10,A38:A92,C38:C92)

Regards,

Originally Posted by Khaldon

=LOOKUP(M10,A38:A92,C38:C92)

That formula requires the lookup range A38:A92 be sorted in ascending order to work correctly.

The equivalent INDEX(...) formula would be:

=INDEX(C38:C92,MATCH(M10,A38:A92))

Or, you can use VLOOKUP if your device supports it:

=VLOOKUP(M10,A38:C92,3)

The first given formula works fine in the laptop.

=INDEX(A:A,INDEX(MAX((C34:C86>=I10)*(E34:E86>=I10)*ROW(A34:A86)),0))

When transferred to an IPAD apple app, the formula returns A1 cell value instead off the resulting formula value from row A34:A86!!

Changing the A:A Index to A34:A86, the formula will now returns cell A34 value regardless.

Is there away please to modify the above formula with INDEX function, and have the formula returns the correct value of row A34:A86 on my device maybe this formula function (A:A, INDEX) is not supported by my app?

Best Regards,

Try this one...

=INDEX(A34:A86,INDEX(MAX((C34:C86>=I10)*(E34:E86>=I10)*ROW(A34:A86)),0)-ROW(A34)+1)

Hard to say what's causing that result.

You could always use a helper column.

Enter this formula in G34 and copy down to G86:

=IF(AND(C34>=I$10,E34>=I$10),1,"")

Then the lookup formula would be:

=INDEX(A34:A86,MATCH(2,G34:G86))