HLOOKUP: gives inaccurate data depending on the formula for lookup value

I ran into this weird problem while trying to work with the HLOOKUP function. I've built a simple case here to demonstrate.

In the column starting in C7, I will enter the hlookup functions (shown in column D). the lookup values are starting in column B. The formulas in column A are the ones I entered in column B.

The table for reference is in the top two rows. The lookup value corresponds to the first row and the corresponding value in the second row is returned a output.

I tried to lookup the value .01 and .02 in the table.

If it worked right, it should return "B" for .01 and "C" for .02

But as seen here, I only get the right answer depending on what formula I use for the lookup value.

For example, in the case of .01, I get the right answer when I use the formulas ".01" and ".31-.3", but not for others such as ".11-.1"

Likewise for .02, I get the right answer when I use the formulas ".02" and ".32-.3". Everything else gives me the value in the cell preceding the right one.

Please let me know what is wrong here!

PonNab

Posted 2017-04-15T19:56:02.420

Reputation: 23

what did you write in B just values or a formula to convert A – yass – 2017-04-15T20:11:59.527

B contains the the formula in A (without the quotation marks). So B9 would have "=0.21-0.2" – PonNab – 2017-04-15T20:20:10.453

Ive updated the screenshot to show what the function for a cell in the B column looks like – PonNab – 2017-04-15T20:24:28.503

=HLOOKUP(ROUND(B15,2),$A$1:$D$2,2) use Round(B15,2) to have the exact number of decimal digit or in B15=Round(0.22-0.2,2) – yass – 2017-04-16T12:52:33.657

It's interesting because "0.11-0.1" and "0.21-0.2" returns A, but "0.31-0.3" returns B – Vylix – 2017-04-16T16:13:34.557

Adi Nugroho - gives the proper solution. I added a comment why it works on the bottom of his solution. The search index is searching INTEGERS or TEXT or STRING values, not double precision numbers or real numbers. It's a search index and so it's fast and not complex and in a lexical order of small to big. – ejbytes – 2017-04-16T19:37:46.657

Answers

I did a bit of experiment, and I found out the best and consistent way is to wrap both lookup value and lookup array in TEXTfunction

TEXT(B4, "0.00") refers to the lookup value, and format the value to two decimals.

TEXT($A$1:$D$2, "0.00") refers to the lookup table, and format all numbers to two decimals.

IMPORTANT! You must use the same format and you must Ctrl + Shift + Enter instead of Enter to input an array formula.

EDIT: A table's search index (the thing it searches for in initial search) is seeking TEXT or INTEGER, not long or double numbers or real numbers.

If you are importing the lookup table or value from text or csv, consider importing them as TEXT. You can convert back to number using VALUE function. Or you can convert to number using other methods.

Vylix

Posted 2017-04-15T19:56:02.420

Reputation: 1 651

I just posted a similar answer, but this suffices just fine so I deleted mine. The reason VLOOKUP and HLOOKUP behaves in the way it does is exactly stated in your solution. A table's search index (the thing it searches for in initial search) is seeking TEXT or INTEGER, not long or double numbers or real numbers. You can put this comment in your solution if you'd like. I voted your solution one-up because it's the correct resolve. – ejbytes – 2017-04-16T19:35:47.073

Also a comment for the OP or a corollary to your solution. The opposite also works. You could use text (as if importing data) as a value in the "headers" of the table instead of double precision numbers. But then you could not search based on math (e.g. A-B=0.001) -- and you'd have to resort to the solution above again. – ejbytes – 2017-04-16T19:42:31.903

One more comment for Adi. Also another "reason" and to include for a complete Answer. When searching and INDEX in a table in a given Excel algorithm such as HLOOKUP/VLOOKUP, an index comprised of double precision numbers (e.g. 0.001, 0.002...) will yield a CEILING result in a non-exact match (e.g. 0.001 becomes int 1, 0.01 becomes 1, or even 0.000001 becomes 1). – ejbytes – 2017-04-16T19:46:13.870

Thank you very much Adi. I ended up writing a long macro to do what I wanted but I'll keep this in mind for the next time. – PonNab – 2017-04-17T01:17:04.370

@PonNab you can mark this answer as the answer by clicking on the tick mark near the vote up button – Vylix – 2017-04-17T02:11:14.760

@ejbytes Is it CEILING ? Isn't it dependent on how the lookup table sorted? Tried LOOKUP for 0.1 in a 0, 0.5, and 1, it returns 0 instead of 0.5 or 1 – Vylix – 2017-04-17T02:13:37.087

@AdiNugroho Ah, I see. I guess I was assuming some on a couple tests. I'm guessing the algorithm isn't as clear as I assumed them. Funny thing after I formatted the cells, tried a few things, and came back to it later... it worked without any special TEXT(...) or anything... as to say we were all wrong and it works plainly. But that isn't the case. Excel has "weird" functionality. Even re-formatting cells doesn't take some of the time unless each cell is refreshed. – ejbytes – 2017-04-17T04:29:22.123

TEXT is the same as formatting the cell as TEXT. You could format the table as TEXT, but you can't use the table for calculation. I purposely didn't include that and leave the original lookup table as it is because the solution should lie in the formula, not altering the table. If someone alter the table, the lookup will be broken. Not with my solution. – Vylix – 2017-04-17T04:36:42.790

HLOOKUP (and also VLOOKUP) has a fourth (optional) parameter, which defines if it either searches for an exact match, or for the last value not larger than the search value; the default is the latter.

Basically it works as designed. Read the help and set the fourth parameter to achieve the result you are looking for.

Aganju

Posted 2017-04-15T19:56:02.420

Reputation: 9 103

Putting Exact match as the fourth parameter gave error values for all the formulas (such as ".11 -.1") ...it only worked where the value was entered directly – PonNab – 2017-04-15T21:32:22.680