Xlookup not working as expected

Apples and pears, or more precisely, text and numbers - they aren't the same!

=XLOOKUP(--F2,I1:I10,J1:J10)

and fix the ranges:

=XLOOKUP(--F2,$I$1:$I$10,$J$1:$J$10)

Glad to have helped.

Just to wrap up: the RIGHT function will return text.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for the rep.

It turns a number stored as text into a real number. The RIGHT function returns text.

You could add the -- to your RIGHT function instead of the XLOOKUP if you preferred.

The double negative coerces the text "number" into a true numeric value. It is, effectively, carrying out two numeric operations on the text value. That is, -1 * -1 * text number = numeric value. There are other ways of coercing the value. You can add or subtract zero ( 0 ), or multiply by one ( 1 ).

You can avoid the helper column:

Formula:

Please Login or Register  to view this content.