Lookup returning wrong values

[darxide23]

I've attached the file I'm using, but with the non-formula related cells blanked for privacy.

The problem is on the "Math" sheet in column A. It's using a Lookup of column J on the "Calculator" sheet against a table. If you enter the Roman numeral equivalents of 0, 1, 2, 3, 4, 5, 6, 7, or 10 then it returns the correct value to column A. If you input the equal of 8 (VIII) then it returns 5.5 instead of 5 and if you input the equal of 9 (IX) then it returns 3 instead of 5.5.

What is happening?

[XOR LX]

Hi,

The range passed as LOOKUP's lookup_vector must be sorted, which, in this case would need to be:

0
I
II
III
IV
IX
V
VI
VII
VIII
X

Regards

[darxide23]

must be sorted

Well that doesn't make any sense. Why would they do it that way?

Is there an alternative to this rather than adjusting the entire lookup table? This one only goes 0 to 10 which is just that one number out of order, but I've got plans on expanding and Roman numerals go all over the place and it will be impossible to manage if I have to treat them as text and sort them alphabetically. For example, if there were a cell format for Roman numerals so that I could just type a "4" into the cell and it would display as "IV" but for sake of any functions Excel saw it as "4" instead. Now that would be perfect.

[bebo021999]

Why did not you try VLOOKUP? The range of vlookup value does not required sorted value, while lookup does.

[XOR LX]

You can use VLOOKUP or INDEX/MATCH with an exact match parameter, e.g.:

=VLOOKUP(Calculator!$J3,$L$12:$M$22,2,0)

which does not require the lookup range to be sorted.

Regards

[darxide23]

That's what I'm looking for. I didn't know VLOOKUP was all that different from LOOKUP. All resources I've read just say how similar and interchangeable they are. Even the Microsoft reference doesn't adequately convey the the sorted/unsorted aspect very well. I just used LOOKUP because it was simpler and I didn't need to lookup the other parts of the table here, just the one column and when you mentioned sorting I just assumed VLOOKUP to be the same.