Troubleshooting Excel VLOOKUP. 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.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2272. VLOOKUP text numbers.
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
Here’s a problem that I’ve covered in the podcast before and I learned to create a new way. We’re trying to do a VLOOKUP here looking at 4399 into this table and it’s not working, right. The formula’s correct, everything looks great but it’s not finding a match. So it’s not finding a match for any of these and the problem as you can see is this green triangle over here. This 4399 is stored as text and that is not seen as a match for this 4399 which is stored as numbers.
Now in the past, on the podcast, I’ve suggested converting this column, text, numbers, real numbers choose the whole column, ALT DEF, bam, they’re all numbers, the VLOOKUP starts to work. But what if you have to leave this as text? Alright. I think I’ve suggested using value of A2 but I was doing a seminar in Orlando and Lyn, Lyn had a much shorter way to go. Just take that A2 and plus zero or I guess you could do times one and it starts to work again. Plus zero is taking that text number and converting it back to a real number and allowing the VLOOKUP to work. I want to thank Lynn for that great idea and I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
Here’s a problem that I’ve covered in the podcast before and I learned to create a new way. We’re trying to do a VLOOKUP here looking at 4399 into this table and it’s not working, right. The formula’s correct, everything looks great but it’s not finding a match. So it’s not finding a match for any of these and the problem as you can see is this green triangle over here. This 4399 is stored as text and that is not seen as a match for this 4399 which is stored as numbers.
Now in the past, on the podcast, I’ve suggested converting this column, text, numbers, real numbers choose the whole column, ALT DEF, bam, they’re all numbers, the VLOOKUP starts to work. But what if you have to leave this as text? Alright. I think I’ve suggested using value of A2 but I was doing a seminar in Orlando and Lyn, Lyn had a much shorter way to go. Just take that A2 and plus zero or I guess you could do times one and it starts to work again. Plus zero is taking that text number and converting it back to a real number and allowing the VLOOKUP to work. I want to thank Lynn for that great idea and I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel
