LOOKUP function

[Walley]

Hi
I use the fuction LOOKUP(1E+100;C6:C25)-C6, to return a value in G10, which
work if there is value in C6:C25, but if there is not any value in C6:C25, it
return the "#N/A", how can I make cell G10 to not show "#N/A", if there is
not any numbers in C6:C25.
Another question, I use the LOOKUP(1E10;C6:C25)-C6, but it change to
LOOKUP(1E+100;C6:C25)-C6 automatically, why, what the "1E10" mean.
Thank in advance for help

 

[Sheeloo]

Use it like this
=IF(ISNA(your lookup formula),"",your lookup formula)

ISNA will be true if lookup returns #N/A and you will get blank in your
cell. If it is not #N/A then you get the result of your lookup.

I did not understand what you mean by 1E10... Is it a string? If yes, then
use "1E10".

I pasted your formula but it was not accepted by Excel due to 1E10...

 

[T. Valko]

Try this:

=IF(COUNT(C6:C25);LOOKUP(1E100;C6:C25)-C6,"")

1E100 and 1E+100 mean the same thing in Excel. It's scientific notation for
a very large number, 1 followed by 100 zeros. The pedantic method is to use
9.99999999999999E+307 but I think this is even more confusing to most users.
How many 9s do I have to enter???????

You may also see something like these being used: 10^100 or 99^99.

LOOKUP(10^100;C6:C25)

Those also generate a very large number but those have to calculate while
1E100 is a constant.

The way it works:

If all the numeric values in the referenced range are less than the
lookup_value the formula returns the *last* numeric value in the range. To
ensure we get the correct result we use a lookup_value that is guaranteed to
be greater than any value in the range so we use an arbitrary gigantic
number like 1E100.

You could also use something like this if it helps to understand better:

LOOKUP(MAX(C6:C25)+1;C6:C25)

 

[T. Valko]

I pasted your formula but it was not accepted by Excel due to 1E10...

It was probably not accepted because the OP's formula uses semicolons as
separators and your regional settings probably use a comma. The separators
are a regional setting.

 

[T. Valko]

Ooops!

=IF(COUNT(C6:C25);LOOKUP(1E100;C6:C25)-C6,"")

Change that comma to a semicolon:

=IF(COUNT(C6:C25);LOOKUP(1E100;C6:C25)-C6;"")

 

[Sheeloo]

Thanks for the information... That must have been the reason...