LOOKUP function does not work with row vector of numbers

  • Thread starter Thread starter JRaSH
  • Start date Start date
J

JRaSH

Here is a spread sheet on Excel 2003:

A B
1 =1.11+2 2
2 =LOOKUP(3.11,A1:B1)

The B2 cell is #N/A, although A1 is evaluated to be 3.11

It seems if you match against 1.1+2, it works.

Could anyone tell me where things is going wrong?

Thanks
 
What are you trying to do?

Do you want to return the *last* (rightmost) numeric value in the range?

If so, try this:

=LOOKUP(1E100,A1:B1)
 
T. Valko said:
What are you trying to do?

Do you want to return the *last* (rightmost) numeric value in the range?

If so, try this:

=LOOKUP(1E100,A1:B1)
Click to expand...

Originally, I want to find out which column has the least value and
display the column name accordingly, like below:

A B
1 Col1 Col2
2 val1 val2
3 =LOOKUP(min(val1,val2),A2:B2,A1:B1)

But I found if val1 or val2 are something calculated from other values
like 'val1 = 1+1.11', this function doesn't work, returning a wrong
value or just #N/A.

In the case as stated in this thread, the LOOKUP returns #N/A, but
actually it should return 3.11.

I don't know where Excel has gone wrong.
 
I want to find out which column has
the least value and display the
column name accordingly
Click to expand...

Ok, you're using the wrong function.

Try this...

Col1...Col2...Col3...Col4
..30.....10............81.

A1:D1 = column headers
A2:D2 = numeric values

=INDEX(A1:D1,MATCH(MIN(A2:D2),A2:D2,0))

Result = Col2
 
Cool. That's what I want. Thanks.

But I'm still curious about LOOKUP.
The description says: LOOKUP returns the number in the vector that
equals or is the largest less than the lookup_value.

So given the cells below:
..2.11...0.
LOOKUP(2.11,A1:B1) returns 2.11
This is true when A1 & B1 are inputed by directly typing '2.11' & '0'

The problem is if A1 is inputed by typing '=1.11+1' (equals 2.11), the
formula returns #N/A.

'2.11' & '=1.11+1' are basicly the same number, why is here such a
difference?
 
For LOOKUP to work correctly the lookup_vector *must be sorted in ascending
order*. Otherwise, there's no telling what result you may get.

Consider this example:

5,7,12,15,25

=LOOKUP(12,A1:E1)

Result = 12

The lookup_vector is sorted in ascending order and there is an exact match
of 12. Now, try this:

=LOOKUP(10,A1:E1)

Result = 10

The lookup_vector is sorted in ascending order but there is not an exact
match of 10 so the result is the closest value that is less than 10.

This is how the LOOKUP function works. Just remember that the lookup_vector
*must be sorted in ascending order*.
 
Hi, thanks for the reply.

But I still see the problem with LOOKUP(2.11,A1:C1) for the following
vector:
..2.11....3....4.
and also with LOOKUP(2.11,A1:A3) for the vector below:
2.11
3
4
Both of '2.11' are inputed by typing '=1.11+1', both of vectors are
sorted ascending and both LOOKUP outputs '#N/A'.

Regards
 
Ok, this is a separate issue. The LOOKUP function is working properly.

Try this...

Enter this formula in cell A1:

=1.11+1

The displayed result will be 2.11

Now enter this exact formula in cell B1:

=(A1-2.11)=0

The result you will get is FALSE but that doesn't make any sense. 2.11-2.11
should equal 0.

Now, enter this exact formula in cell C1:

=(A1-2.11)

The result you will get is 4.44E-16. This is a very small number. Formatted
as number to 30 decimal places the number is:

0.000000000000000444089209850063

This is commonly referred to as a "rounding error". It is the result of
computers doing binary arithmetic on decimal numbers. The explanation of why
this happens is very technical and I probably can't explain it so that you
would understand it.

See this for an explanation:

http://www.cpearson.com/Excel/rounding.htm
 
Back
Top