LOOKUP Function Replacement

[Khaldon]

My apple app does not support LOOKUP function!

The following link has all the supported functions by my apple app.
http://support.dataviz.com/support_i...l#dtg_iphone21

I need help please with a replacement for the LOOKUP function of the following formula:
=LOOKUP(2,1/(($C$38:$C$92>=$I10)*($E$38:$E$92>=$I10)),A$38:A$92)


Thank you,

[XOR LX]

Hi,

Does it support array formulas?

If so:

If your entries in column A are numerical:

=INDEX($A$38:$A$92,MATCH(10^10,IF(($C$38:$C$92>=$I10)*($E$38:$E$92>=$I10),A$38:A$92)))

If your entries in column A are text:

=INDEX($A$38:$A$92,MATCH(REPT("z",255),IF(($C$38:$C$92>=$I10)*($E$38:$E$92>=$I10),A$38:A$92)))

Regards

[Khaldon]

My entries in column A are numerical, but unfortunately the formula is not working, it reflect ####

[XOR LX]

Well, it was difficult to make guesses without seeing a sample worksheet. Did you ensure that you entered it as an array formula?

Other than that, without seeing a sample, it will be very difficult to help you.

Regards

[Khaldon]

Please refer to the following link for a sample.:

http://www.excelforum.com/excel-form...html?p=3368252

Regards,

[XOR LX]

The first formula I gave you (with the ranges amended to suit the example to which you directed me, of course) gives precisely the same answer as the formula offered you by benishiryo in that post.

Regards

[FDibbins]

Please upload a sample file here. That link goes to another link, and I dont know about other members, but Im not going to search through a bunch of links for a workbook that you could just as easily upload here

[Tony Valko]

Try this...

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

[Khaldon]

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,

[Tony Valko]

=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)

[Khaldon]

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,

[Tony Valko]

Try this one...

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