How to make function VLookup take decimal numbers expressed in whole?

English
#1

I have a cell that shows the average number of a series of numbers. Sometimes, such average is a decimal number, but the format is set to show it as a whole number by using the “delete decimal numbers” option in the menu. It’s all fine, so far.

The problem is that, I have another cell to show some information based on that number via the VLOOKUP function. BUT, when the result of the average is a decimal, and it’s shown as a whole number, the VLOOKUP function shows a “N/A” error (because the array where it looks for the information only has whole numbers).

I hope you understand what I mean.

What I want is to make the VLOOKUP function to look for the number shown (for example, 17) and not the real number (16,5) that the average really gives, but gets rounded up to 17 and is shown as 17 in the corresponding cell. How can I do that?

#2

You talk in riddles … if you want the rounded number, so use … ROUND(number)

1 Like
#8

Sorry, English isn’t my first language and I’m not familiar with LibreCalc terminology.

#3

I would hope such a dangerous option didn’t exist. Where (exactly) did you find it? What would be the content of afflicted cells after the action?

#6

Captura de pantalla de 2021-12-02 11-40-23

#9

Do you think this is an answer to my question?
This kind of “conversation” won’t lead anywhere.

Well, I was very persistent in this case:
Of course, I’m using a different surface, and couldn’t be sure what you actually meant. You also are supposed to use a different language. You talked of a menu instead of a toolbar.
Of course, I know that you can show lots of next to useless “icons” in the formatting toolbar.
Since I don’t use them, I didn’t know about the icon coming up with the hint “Delete Decimal Place”.
Now I had to investigate for another few minutes to find it, and to make up my guess this was what you meant by “delete decimal number”.
Of course, the hint is gravely misleading.The action doesn’t delete anything, but only changes the appearance. (If I pull my shutter down, I’m still here, though you can’t see me through the window.)

Conclusion: We shouldn’t talk about icons and hints, but about actions. The menu tree can be a help. Icons rarely can. Yes, I know. That’s difficult given the way people are introduced to software mostly. We should improve that. Teachers listening?

Here (Number formats): Don’t click icons, but define a cell style for vevery number format you actually need, and apply them to the respective cells. (As a rule a complete column should have the same NF.)
Generally: Reduce direct formatting.

Have a lot of fun with Calc (used the conscious way).

#13

:smile:
I looked (for my information), and yes… Delete Decimal Place is the real name for this button.
There is only one more place where you can change the number of decimal places displayed.

#15

Well, thank you for your insight. Now I know what name to call that “toolbar” and that the “hint” on that button reads “delete decimal place” in English; in Spanish it’s just “eliminar decimal” which would translate as “Delete decimal”, although as you so wisely mentioned,

1 Like
#4

If so, and you don’t just use number formatting to display a rounded value which doesn’t change cell content, then =VLOOKUP(ROUND(number);array;0). Or you’re omitting the 3rd parameter to VLOOKUP() (or give it a non-0 value) that makes it expect a strictly sorted in ascending order array for a range lookup, but your array is not sorted. You didn’t give details in your question, so these are just guesses.

See VLOOKUP help.

1 Like
#7

Thank you. This solved the problem.

#12

In what way?

#14

I mean, it did what I wanted it to do. I’m not an expert in Calc or software in general, I do not know the terminology you use to talk about your issues in a technical way.

What I wanted is the VLOOKUP function to take, accept, recognize, (I don’t know what action verb you would use here) a number that is in reality a decimal number (for example 16,5) but is displayed in the cell as (17) because the cell is formatted to “delete, reduce, hide”, (again, I do not know what technical verb you would use here) decimals and to look for that number (17) in the array and not (16,5) because in the array all numbers are whole numbers, not decimals.

Your message was of great help. I had to introduce the ROUND function, option (once more, I do know what technical name this has).

#5

t71102.ods (33.1 KB)

#10

Cute demonstration. So, I wondered: What if MATCH returns #NA. I kept going until that happened.

To my surprise, things seem to break. Until reloading the sheet completely (even beyond hard re-calculate), once HYPERLINK hits an #N/A it no longer works. The new cell address shows after F9 or Ctrl+Shift+F9, but there is no more hyperlink goodness. (Also, the hyperlink “comment” does not show up upon hover.)

Can you duplicate this behavior? (Obviously, reducing A1000 will speed up the scenario).

#11

Indeed, once the link is broken, it is broken “forever”.

1 Like
#16

Thank you for trying to help me, if that’s what you intended to do, although I do not understand what to do with that sheet. I’m no expert. If I were, I wouldn’t have had such a simply-solved problem as I did.

#17

A1 has a random seearch value. The data list has random values. The lookup finds the first match in the data list after rounding both, the search value and the data list values, regardless of sort order. You hit F9, all values change and hyperlink jumps to the matching value. MATCH works in the exact same way as VLOOKUP. It returns the matching position number which is needed for the hyperlink whereas VLOOKUP would return some value from another column in the same matching row. This solution is different from the ordered LOOKUP. The ordered [V/H]LOOKUP matches at the last position where the search value is equal to or smaller than the search value.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.