Forum Discussion

andivogli's avatar
andivogli
Occasional Reader
Nov 06, 2025

Vlook up issue related to format of cells between two different files

I want to apply the vlook formula for working purposes, the issue is :

 

I have two files

  1. file A Nace codes
  2. file B analysis

There are 616 rows in file A nace codes while in file B there are 32. When I apply the vlookup formula to find which of these 32 records from file B analysis are found in file A nace code i get no results (always #N/A). I have tried changin the format of the columns still the same results in order to have the same format but still the same results

formula applied =VLOOKUP(B2,'[file B analysis.xlsx]Same G diff Code same Name 32'!$B$2:$F$33,5,false)

 

 

 

 

excel

2 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Nov 07, 2025

    alternatively you can try:

    =VLOOKUP(TEXT(B2,"0000"),'[file B analysis.xlsx]Same G diff Code same Name 32'!$B$2:$F$33,5,false)

    BTW if you have excel 365 then yuo might want to consider updating to using XLOOKUP

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 07, 2025

    I suspect that your 'tiger codes' in file B are texts whereas the 'name codes' in file A are numbers. Simply formatting both columns as Number does not change a text to a number or vice versa. VLOOKUP will thus not find a match and always return #N/A.

    Probably easiest to transform column B in file A to text with Text-To-Columns. You find the icon on the Data ribbon.

    Save you file before doing this so that you can always revert back it in case it doesn't work as expected!

    Select B2:B616, Text to columns, step1 change nothing, next, step 2 change nothing, next, step 3 set the column data format to Text, Finish.

    Now the name codes in file A are all texts and VLOOKUP should be able to find matching codes from file B.

Resources