vlookup and formatting

smking204

New Member
Joined
Jan 28, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I'm having trouble getting vlookup to recognize a lookup value. Here's the situation:

  • I built a table and changed the numbers to text format
  • I built a vlookup: =VLOOKUP(H1, 'Tables - Extra'!B:D, 3, 0)
  • I've verified that my table array ('Tables - Extra'!B:D) is working just fine, as far as I can tell.
  • No matter what I try, the result is #N/A as long as I'm referencing H1 or any other cell in the table for my Value Lookup
  • Now, I've copied my table and pasted values only into a new sheet, trying to start fresh.
  • In this fresh sheet, where every value is pasted as general format, I get the same problem, until I retype the number in the cell--suddenly vlookup will recognize it now. I can even change the format to text, or leave it as general.
  • Thinking I've done something to remedy the problem, I go back to the original table and try the same thing, retyping my values, trying various format changes... with no luck.
What is causing this hang-up? What is a quicker way to fix it than retyping and reformatting my entire table? I hope this has been enough information, thanks in advance for the replies!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Note that in order to make a VLOOKUP work, the values you are comparing/matching MUST be the same data type.
You can only compare numbers-to-numbers, and text-to-text.

Changing the format of a column AFTER the data has been entered will NOT change the format of those data entries!
It would need to be re-entered (like you saw when you re-typed them).

However, you can change the data of the whole column at once by selecting that column, and choosing "Text to Columns" from the Data menu.
Once there, go to step 3, and you can choose "General" if you want to make the values numbers, or "Text" if you want to make them text.

So, follow that process to make the two lists compare like data types, and everything should work out.
 

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
Or another way to change number which appear as text

Type 1 in any blank cell => Copy => Select the range which you want to change => paste special => value and multiply => Ok
 

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
120
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I was able to reproduce the error, and did this:
Excel Formula:
=VLOOKUP(TEXT(F2,"0.00"),B1:C29,2,FALSE)

but the formatting has to work exactly. so 2.12 will lookup in this example, but 2.1 won't. If you can format your lookup table so it's always the same, it could work.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,321
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top