Lookup Not working as expected

  • Thread starter Thread starter trumpy81
  • Start date Start date
T

trumpy81

GDay All,

Can anyone tell me why the folling function is not working correctly.

=LOOKUP(MIN(R10C4:R63C4),R10C4:R63C4,R10C3:R63C3)

The function works only if the lowest value falls within certain cells, but
returns #N/A if the lowest value is in another cell.

Any help would be appreciated.

TIA
 
Hi trumpy81,

As far as I know, LOOKUP only works correctly if the lookup range is sorted
in ascending order 1,2,3, etc. or a,b,c,etc. If you need to lookup in an
unsorted range then you can use VLOOKUP, HLOOKUP (both with the fourth
argument set to 0 or False) or MATCH.

Regards,
KL
 
KL said:
Hi trumpy81,

As far as I know, LOOKUP only works correctly if the lookup range is
sorted in ascending order 1,2,3, etc. or a,b,c,etc. If you need to
lookup in an unsorted range then you can use VLOOKUP, HLOOKUP (both
with the fourth argument set to 0 or False) or MATCH.

Regards,
KL
Click to expand...

GDay All,

Thanx KL for the help. I tried using VLOOKUP and HLOOKUP but I seem to get
the same results.

Anybody have any other ideas??

I need to first of all find the lowest value in a column, then display the
contents of the cell beside the cell that contains the lowest value, which
actually contains a name.

The cells actually contain times, as in eg: 9:30:00am.

Any help is certainly appreciated.

Thanx again KL
 
I dunno for sure (Row and column referencing gives me a headache) but it
looks like you are trying to use VLOOKUP to find the value to the LEFT of
the lookup column......it don't do that, only to the right...........for
example, this formula works...........
=VLOOKUP(MIN(C:C),A:B,2,FALSE)
it finds the minimum value in column C, and looks it up in the range A:B and
returns the value in column B that is appropriate.
it could work also as
=VLOOKUP(MIN(A:A),A:B,2,FALSE)

hth
Vaya con Dios,
Chuck, CABGx3




TK2MSFTNGP12.phx.gbl...
 
Hi,

As per CLR note (I didn't notice it as I am not used to R1C1 notation) you
can't use VLOOKUP/HLOOKUP, but you may try this formula:

=INDEX(R10C3:R63C3,MATCH(MIN(R10C4:R63C4),R10C4:R63C4,0))

Regards,
KL
 
KL said:
Hi,

As per CLR note (I didn't notice it as I am not used to R1C1
notation) you can't use VLOOKUP/HLOOKUP, but you may try this formula:

=INDEX(R10C3:R63C3,MATCH(MIN(R10C4:R63C4),R10C4:R63C4,0))

Regards,
KL
Click to expand...

GDay All,

Once again KL ... THANX!!

That formula did the trick and saved me from a lot of boring/confusing macro
coding!!

It works a treat :)
 
Back
Top