Hlookkup/Match won't work

[Guest]

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,Levels!$A$7:$A$1800))>0,1,22)
and
=IF(HLOOKUP(P21,Levels!$C$7:$O$1800,MATCH(O21,Levels!$A$7:$A$1800))<0,1,22)
but the returned for both < & > value is always 22.

Thanks for you time

 

[Guest]

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.

---

 

[Guest]

Since you are using HLOOKUP with the range_lookup set to TRUE

TRUE by its omission in your HLOOKUP, I mean

---

 

[Guest]

Thanks Max
HLOOKUP top row ascender order was my problem.

Appreciate your time and help.
Have a great day

Brian

 

[Max]

Glad that nailed it for you, Brian.
Thanks for the feedback ..