Hi there. Any help much appreciated

My formula HLOOKUP(P21,Levels!$C$7:$O$1800,MATCH(O21,Levels!$ A$7:$A$1800))
has worked fine for the last 10 years. All values in cells C7:O1800 have been
0.

My problem is, I now need to add a mixture of negative values (-20) to the
above range and my formula just won't return the negative value

I have tried the following formula to test
=IF(HLOOKUP(P21,Levels!$C$7:$O$1800,MATCH(O21,Leve ls!$A$7:$A$1800))0,1,22)
and
=IF(HLOOKUP(P21,Levels!$C$7:$O$1800,MATCH(O21,Leve ls!$A$7:$A$1800))<0,1,22)
but the returned for both < & value is always 22.

Thanks for you time

Since you are using HLOOKUP with the range_lookup set to TRUE, you need to
ensure that the HLOOKUP's top row, ie values within: C7:O7
are sorted in ascending order, left to right.

From Excel's help:
If range_lookup is TRUE, the values in the first row of table_array must be
placed in ascending order: ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE;
otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BrianW" wrote:
Hi there. Any help much appreciated

My formula HLOOKUP(P21,Levels!$C$7:$O$1800,MATCH(O21,Levels!$ A$7:$A$1800))
has worked fine for the last 10 years. All values in cells C7:O1800 have been
0.

My problem is, I now need to add a mixture of negative values (-20) to the
above range and my formula just won't return the negative value

I have tried the following formula to test
=IF(HLOOKUP(P21,Levels!$C$7:$O$1800,MATCH(O21,Leve ls!$A$7:$A$1800))0,1,22)
and
=IF(HLOOKUP(P21,Levels!$C$7:$O$1800,MATCH(O21,Leve ls!$A$7:$A$1800))<0,1,22)
but the returned for both < & value is always 22.

Thanks for you time

Since you are using HLOOKUP with the range_lookup set to TRUE

TRUE by its omission in your HLOOKUP, I mean
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks Max
HLOOKUP top row ascender order was my problem.

Appreciate your time and help.
Have a great day

Brian



"Max" wrote:

Since you are using HLOOKUP with the range_lookup set to TRUE

TRUE by its omission in your HLOOKUP, I mean
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad that nailed it for you, Brian.
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BrianW" wrote in message
...
Thanks Max
HLOOKUP top row ascender order was my problem.

Appreciate your time and help.
Have a great day

Brian