Look up a value that may be formatted as text or a number

I've inherited a number of very old workbooks and they're a rats nest of links and lookups. I'm tidying them up and trying to get some structure into the sheets. One issue I'm continually running into is format mismatch. I'm using this formula currently which works for what I need:

=ifna(vlookup(A1&"",data!$a$1:$b$2000,2,0),vlookup(value(A1),data!$a$1:$b$2000,2,0))

But I'd like something tidier if it existed.

Is there an easier way to search text as numbers and numbers as text without changing the source data?

Solution 1:

To search text as numbers, use =NUMBERVALUE(). That will convert numbers saved as text into a number and it won't have any effect on numbers saved as numbers. If a cell contains letters, it'll return a #VALUE! error.

To convert numbers stored as text into numbers, you can also use a double negative like this. Two negatives make a positive, but it also converts text like '1000 into a number like 1000.

=--B3

To search numbers as text, use =TEXT(). That will convert numbers to text. Check the help menu for the options.

You can use those in formulas so that your lookups won't get confused by the mismatch. For example:

=VLOOKUP(NUMBERVALUE(B3),B:B,1,FALSE)
=VLOOKUP(TEXT(B3,"#"),B:B,1,FALSE)