Need help with what I thought was a simple VLOOKUP

English
#1

I have a sheet that has a table with height in inches in the first column (it starts with 16" and goes up by 6" from there up to 106") and the second column has a number from 1 to 16.
I created a VLOOKUP to get a number in the second column based on the number in inches in the first column. This works fine up to 99" but if I have anything above 99" I get a #NA result. I have tried different things with the last being an IF function but I can’t get that to work with the inches mark (") after each number that is needed.

Note that originally I wanted to do something like feet and inches but I could not figure out how to do something like 5’11" as opposed to using 71" that would work in a VLOOKUP function and look at the numbers that fall between every 6".

#2

So why not do the VLOOKUP using inches and create another column that converts the result to feet and inches so you can hide the VLOOKUP column? The conversion can be done something along these lines =INT(A3/12)&"' "&MOD(A3;12)&"″" (note, I used Double Prime, not straight quotes for inches although you could use 4 straight quotes in a row)

#3

I’m not sure how works. The VLOOKUP is on a page called tables that has a lot of tables that use the VLOOJUP function in many different formulas. It doesn’t need to be hidden.
I just wish this would work using the numbers I already have.
All the numbers in the table have the inch (") symbol after them and it seems the lookup function has an issue with anything above 99"

#4

And nobody have found this in the few years from StarOffice to LibreOffice? Possible, but I usually start with checking my data…
.
Maybe some auto-conversion detected decimal degrees, minutes etc ant this conversion is not triggered for 100".
Slightly different charcters like ' and ’ are also possible…
.
Wich is your locale?

#5
  • Which inch symbol? Strictly speaking, a foot is single Prime and inch is Double Prime although straight quotes are often used. Sometime straight quotes get converted to typographical quotes as on this site.
  • Are they all the same symbol? Could some be two single quotes
  • Could there be a space between the number and the mark in some cells?

I did a check to see if VLOOKUP worked with text looking up formatted numbers and vice versa, found no problem.

#6

I suspect, that your lookup column contains text, not numbers. VLOOKUP in default settings expects the source to be sorted. If you use text, then 99" is larger than 106" because 9 is larger than 1. Thus a series 98", 99", 100", 106" is not sorted, if the entries are text.

If you could catch the exact value in content, then you can use VLOOKUP with FALSE() as forth parameter. Then the lookup column does not need to be sorted.

I wonder why your source does not has numbers. Showing a unit could be done by formatting.

1 Like
#7

It has never worked for me. I have formated and reformated the table and have gone through various formulas and nothing seemed to work. Until I not only removed the inch (") from the table but also split the table into two different ones with it going from 6 to 94 and a second table to go from 100 to 124.
Only then did this final formula finally work: =IF(VALUE(LEFT(Ht,LEN(Ht)-1))<100,VLOOKUP(Ht,MoveRate,2,2),VLOOKUP(Ht,MoveRate2,2,2))
Where MoveRate and MoveRate2 are the separated tables. It started working at one point but the results from column 2 of the table did not always match what it was supposed to be.
EDIT: the info (cell) it’s looking up still has the inch (") symbol in them because it is needed.

#8

See my reply above.
I formatted my table as text and as numbers at different times but nothing seemed to work.
I got my answer from Claude AI but it took some time with testing and showing what worked and going back and forth.
Using what Claude AI did I can format it either as numbers or as text and it works fine either way. What’s interesting is that the cell the formula gets the info from still has the inch (") symbol but the table does not.

#9

Do you need it sorted? Otherwise, if all the inches are formatted numbers it works OK. See attached
VlookupTextvsFormattedNumber.ods (18.6 KB)

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