Hlookup function error 1

[jetboat]

I am trying to use the Hlookup function in Xcell and I'm haveing trouble. the lookup value is from a cell that used the roundup function and it keeps giving me "NA". I did the same formula using Hlookup with the round down function and it works perfect. Can anyone shed some light? I have attached the xcell sheet to view the formula.Cell E17 is the cell with the error.

Thanks in advance!

 

[magoo2]

If you change the argument in E17 from false to true, I think it will work.

Also, in E20, I'm not sure why you have all the parentheses. Try to clean it up to E17 -( )/( )/( )

 

[jetboat]

Magoo2,

Thanks it works perfect now! Do you know the logic behind true vs false in this case?

 

[magoo2]

No, I'm afraid I don't. Usually I read the logic behind the arguments and my understanding leads me to always use the wrong one. So I usually try them both ways.

 

[IDS]

From help on HLOOKUP:

"Range_lookup is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned."


So the question it is asking is "do you want to accept an approximate match?"

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[sdz]

Also if the True/False is omitted your list must be sorted in ascending order.