Lookup Not working as expected
 

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


--
Regards
trumpy81

*** Please remove the `_ spam _' trap before replying to this message ***

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

"trumpy81" wrote in message
...
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


--
Regards
trumpy81

*** Please remove the `_ spam _' trap before replying to this message ***

KL wrote:
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

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


--
Regards
trumpy81

*** Please remove the `_ spam _' trap before replying to this message ***

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...
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


--
Regards
trumpy81

*** Please remove the `_ spam _' trap before replying to this message ***

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:R6 3C4,0))

Regards,
KL


"trumpy81" wrote in message
...
KL wrote:
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

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


--
Regards
trumpy81

*** Please remove the `_ spam _' trap before replying to this message ***

KL wrote:
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:R6 3C4,0))

Regards,
KL

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


--
Regards
trumpy81

*** Please remove the `_ spam _' trap before replying to this message ***