Each result returns #N/A When Using VLookup

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
Office Version
  1. 365
Platform
  1. Windows
When I try using VLookup each result gives #N/A. The format of the numbers are like:-

122213M15707430247002
122218M15707430247003
122219M15707430247004

The numbers on the left are on both files but I don't get the numbers on right on the destination file just #N/A? I have gone to file, options, format and checked it is set to automatic? I have been using VLookup for years and know how to do it,
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Most likely those 'numbers' on the left are numeric in one file and numbers stored as text in the other.
 
Upvote 0
you can use ISTEXT() or ISNUMBER() or , in this example, B4=G4, so for example
Book1
BCDEFGHIJK
2first filesecond fileor
3
4122213M15707430247002FALSE122213FALSETRUE
5122218M15707430247003FALSE122218FALSETRUE
6122219M15707430247004FALSE122219FALSETRUE
7
Sheet1
Cell Formulas
RangeFormula
E4:E6E4=ISTEXT(B4)
I4:I6I4=ISTEXT(G4)
K4:K6K4=B4=G4

where yellow1 must be the same as yellow2

hope it's clear
 
Upvote 0
But how do I get the result of second column onto second file?
 
Upvote 0
And to convert all of the numbers stored as text, you can follow the below steps and convert them to general format. Select the corresponding column, "go to Data Tab in the Ribbon, Choose Text to Columns, Select Delimited, Click Next, Check Box before Tab, Click Next and then click Finish". All of your numbers stored as Text would be converted to General Format and then your VLOOKUP formula will work properly.
 
Upvote 0
And to convert all of the numbers stored as text, you can follow the below steps and convert them to general format. Select the corresponding column, "go to Data Tab in the Ribbon, Choose Text to Columns, Select Delimited, Click Next, Check Box before Tab, Click Next and then click Finish". All of your numbers stored as Text would be converted to General Format and then your VLOOKUP formula will work properly.
I would say, do this to both of your unique identifying columns i.e. the lookup value & match value columns. Once your numbers are formatted as one, your regular VLOOKUP formula will work properly. Please try this and then update with the outcome. Good Luck
 
Upvote 0
How do I get round that then please?
Force a conversion to numeric for both the lookup value and the looked-up range. e.g., =XLOOKUP(--N2,--J2:J4,K2:K4,,0,1)

If it is possible for the data to be alphanumeric, force both to be text =XLOOKUP(N2&"",J2:J4&"",K2:K4,,0,1)

If you know which one is text and which one is numeric, convert only one of them.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top