Lookup Question

[mahmed1]

Quick other question how can i lookup the last value in a range that is visible only

i.e i have this formula

=LOOKUP(9.99999999999E+307,'Group Fixtures'!$C$10:$C$24)

Now depending on how many teams i have the range c10:c24 can go to say c10:c20
c21 - c24 get conditionally formatted to white however the value is still there in the background therefore the lookup still looks up the range from c10:c24. What i need is to lookup the last value in visible range even if the other values are conditionally formatted as white (Not blank).

Many Many Thanks

 

[mahmed1]

That'll probably work but I would use 1E100 instead of 9.9999999999E+307.

=LOOKUP(1E100,INDIRECT("F7:F"&J5))

I can't tell what range you need to look at. In your first post you refer to $C$10:$C$24 and in the second post you refer to $D$10:$D$24 and in the last post you refer to F7:F"&J5.

What's in J5? This formula:

=SUMPRODUCT(--($D$10:$D$24<>"")*--($D$10:$D$24>0))

Better written as:

=SUMPRODUCT(--($D$10:$D$24<>""),--($D$10:$D$24>0))

Maybe this is what you need:

=LOOKUP(1E100,F7:INDEX(F7:F100,J5))

Or, maybe even simply:

=INDEX(F7:F100,J5)

The latter, that is, 9.99999999999999E+307, is a constant standing for a limit in Excel, not a personal whimsey. What follows is a quote from Excel's Help, searchable under 'limits'...



The above is consultable at all times.

One would hope that MS would give such constants names like BigNum (or MaxNum). This would help to eliminate zillions of variations.

Here some links on the BigNum and how/why it's used in look up functions
that are driven by a form of binary search:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)

An additional note

[1]

=LOOKUP(9.99999999999999E+307,A2:F2)

is the same thing as...

[2]

=LOOKUP(MAX(A2:F2)+Delta,A2:F2)

where Delta is a tiny number, say, 1.

Why the first formula then? Because it is faster (no extra function) than the second formula and the second formula won't survive if A2:F2 houses any error value which MAX can't cope with.

Any difficulty remembering 15 9's...

Consult Help for "limits";
Define a name like BigNum as referring to it;
Use a truncated version like 9.99E+307 in analogy to 3.14 which is known as Pi.

Why go to the trouble of an extra unnecessary step of creating a defined name when simply using 1E100 will work just as well?

Can you show us a "real world" example where using 1E100 does not do the same thing as using 9.99999999999999E+307 ?

Thank you both

 

[T. Valko]

Originally Posted by T. Valko
Can you show us a "real world" example where using 1E100 does not do the same thing as using 9.99999999999999E+307 ?

Thank you both

You're welcome!

I guess we shouldn't hold our breath waiting for a "real world" example!