Excel Text Transformation: Troubleshooting VLOOKUP When Numbers Stored as Text - Episode 2272

Microsoft Excel Tutorial: Troubleshooting Excel VLOOKUP when numbers stored as text.
You enter a VLOOKUP but all of the numbers are returning the #N/A error. You can see the 4399 in the lookup table, but here is the difference. The number in A2 is a text 4399 and the number in the lookup table is a number. Today, a new way to solve this with just two extra characters.

Welcome back to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we'll be discussing how to use VLOOKUP with text numbers. This is a problem that I've covered before, but I recently learned a new and more efficient way to solve it.

So here's the issue - we have a VLOOKUP formula looking for the number 4399 in a table, but it's not finding a match. Everything seems to be correct, but upon closer inspection, we see a green triangle indicating an error. The problem lies in the fact that the number 4399 is stored as text, while the numbers in the table are stored as actual numbers.

In the past, I've suggested converting the text numbers to real numbers by using the ALT DEF shortcut. This works, but what if you need to keep the numbers as text? Well, during a seminar in Orlando, a participant named Lyn shared a much simpler solution. Instead of converting the entire column, you can simply add a zero to the end of the text number. This will convert it back to a real number and allow the VLOOKUP to work its magic.

I want to give a big thank you to Lyn for sharing this great tip with us. And as always, thank you for tuning in to the MrExcel podcast. Don't forget to subscribe and hit that notification bell to stay updated on all our latest episodes. We'll see you next time for more Excel tips and tricks.

#excel
#microsoft
#microsoftexcel
#exceltricks
#excelhacks
#excelformula
#excelvlookup
#excelbug
#evergreen

This video answers these common search terms:
how to format excel for vlookup by number
why vlookup not working in excel
why is my vlookup not working in excel
how to fix excel vlookup not working error?
why is a vlookup formula not working in excel
how to remove #n/a error in excel vlookup
how to remove #n/a in excel vlookup formula
how do i clear the n/a on a vlookup excel
how to get na on vlookup in excel
how to eliminate na in excel vlookup
how to fix #na in excel vlookup
how to remove na from excel vlookup formula
make text a number in excel
how to text cells to number in excel
how to text to number in excel
how to make text into numbers in excel
how to turn text into number excel
how to change text in excel to numbers
how to change text into number in excel
how to change text to a number in excel
how to change text values to numbers in excel
how to format text as a number in excel
how to convert text as a number value in excel
how to convert text data in excel to number
how to convert text string to numbers excel
how to convert text to number in excel
how do i change text to numbers in excel
how do i convert text to numbers in excel
how to turn a number into text excel

Table of Contents:
(00:00) Issue with VLOOKUP not working due to text and number mismatch
(00:12) Solution: converting text to numbers using ALT DEF
(00:36) Alternative solution Add Zero
(01:05) Clicking Like really helps the algorithm

[excelisfun]

Thanks Mr Excel !!!! One yes, the old Text Numbers in look, I once did an Accounting Seminar and when I showed +0, then all stood up and applauded becasue this problem had pledged so many of them for so long...

[mattschoular]

VLOOKUP can be a pain sometimes. Great trick to address formatting hurdles . Thanks MrExcel and Lynn.

[BillSzysz]

Or just add two minuses (two cons) before A2 in the VLOOKUP function.
If lookup table contains text and Lookup value is an number then use A2 & "" as a first argument of the VLOOKUP function

[jegobenjamin]

Wow...Thank you so much, my problem is solved.
I looked for 6 hours to resolve this and find this video for 1: 14 minutes.

[farhanjafar]

Thanks it's working. ❤️ From India 🇮🇳

[Fazz-vtzf]

Thanks SO MUCH !!! i was going very crazy and spent 3 hours pulling my sheet apart - Excel is Great but sometimes a real pain where i need medication 😞

[patrikb]

hOLY JUMPING SHITBALLZ! I had this exact problem, I do a search and find this video which solves the problem immediately!


First time in history!!


Thank you!

[sdesigns-bringingyourvisio]

VLOOKUP is a serious pain thanks so much for this trick

[MySpreadsheetLab]

I love the +0 ! And as Bill S says the &"" for the opposite. As long as the two columns in question are consistent then it's quick to solve 😎

[wayneedmondson]

Hi Mr. Excel.. great trick. My goto is the one below from Bill Szysz.. the double unary "--". Thumbs up!

[UShruPra]

Hello,
Thank you so much for this idea, could you please explain logic behind this +0, like how its work, plz.

[Emulives]

When I make a Macro to concatenate two colum in a third colum, its possible without VBA to make that macro flow throught to a variable number of cells (Some has 500 entrys and others have 4000). Today I make the macro flowing in the larger number but I have later to delete that extra cells.

[abibeniam]

Would this trick work if the employee ID had leading zeros ?

[Memoreno]

I have a question, let's say I have both numbers and text on column A (i.e 4888, G548)? I get #VALUE! on the G548 cell. Any help to fix it?

[MazenJoha]

Sumif doesn't seem to have this problem, it doesn't mind numbers as text, why?

[granand]

I have other problem, the table is from power BI in table format and Text, the source of my data to pull and lookup value is number. Any ideas

[orlandomezquita]

I've also used =VLOOKUP(--A2, ...)
-- is the same as multiplying by 1 = (-1)*(-1)

[K_C]

Didn't work for me, I have a number+letter combo (123AB)

[joshhfletchh]

Can anyone help me? i'm searching for a name and trying to return a number, can't figure it out.

[m.mahesh]

Sir please tell me
serial numbers B1:B2 = 1-2 and B3:B4-1-2 like continuous more. sir please give me one video sir please please...