Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.

[OwenNeedsVBAHelp]

Hi,

Thanks in advance for your time.

Im trying to match zipcodes in one column with zipcodes in another column in an array, to then return an associated value in another column in the array. I managed to make it work using vlookup with approximate values, but would prefer to use identical values so I'd like to understand what went wrong.

The zip codes are all identical in format - I used two different methods for this and both worked to produce identical formats, but exact match with vlookup and index(match) still didn't work. First I used =clean(text(zipcode, "00000") on both columns, and formatted both as the special zipcode format. I then tried =trim(zipcode) on both columns. For both methods I made sure to eliminate trailing spaces so each column has identical zip codes, in the same format, with exactly 5 characters. Neither solution worked for the identical match.

The Vlookup function I used was:
=vlookup(zipcode, $g$2:$n$100, 8, FALSE).
Where column G has zipcodes and column N has the desired return value.
The zipcode lookup value corresponds to an identical value in g2:g100, but the formula still returns NA instead of the corresponding value in column N.

The Index(match) I used was:
=index($n$2:$n$100, match(zipcode, $g$2:$g$100, 0), 1)
Where column G has zipcodes and column N has the desired return value.
This also returned NA instead of the corresponding value in column N.

I'm not sure what could be going wrong here, am I missing something? Is there another common vlookup/index(match) error that produces N/A results? Does anyone know a formula to consistently make sure zip codes in two columns exactly match each other?

Thanks again for taking the time to read.

 

[Skybluekid]

How were the data in the two columns entered?
I would copy a value from Col G and the same value in Col N, paste them in two cells and check that there is no difference.

 

[nemmi69]

Check whether "zipcode" format is the same as the column with zipcodes.

 

[OwenNeedsVBAHelp]

How were the data in the two columns entered?
I would copy a value from Col G and the same value in Col N, paste them in two cells and check that there is no difference.

One column was exported from browser, one was copy pasted from text, then all values in both columns were put through the formulas I included in my post to make sure they were identical. I have checked by manually comparing values across columns as you recommend and they were identical in every case I checked. All values are 5 digits, zip code special number format, no trailing or leading spaces.

 

[OwenNeedsVBAHelp]

Check whether "zipcode" format is the same as the column with zipcodes.

I did this, twice, using a different method each time, and manually spot-checked afterward. In both cases values were consistently identical - 5 digit zip codes using the zip code special format with no leading or trailing spaces. See the third paragraph of my post to see how I did this.

 

[Alex Blakenburg]

Are you checking the length using the =Len ?
When you checked if they were identical did you did you use Excel to do the comparison ie =G2=Zipcode_cell ?

I can't tell whether both column G and the source of the zip code are on the same sheet.
On each relevant sheet try running the code below.
(the 160 is the more likely)

Your current functions are converting the zip codes to text, the below may convert it to a number.
Apply a Custom Number format if you want to see the leading zero this will keep it as a number.

Sub RemoveWebCharacters()

    ActiveSheet.UsedRange.Replace what:=ChrW(160), replacement:=""
    ActiveSheet.UsedRange.Replace what:=ChrW(8203), replacement:=""

End Sub

If you are looking up text to number or number to text (ie inconsistent lookup value and lookup array data type) we might need to also use something like:

=XLOOKUP(TRIM(A2),TRIM($G$2:$G$100),$N$2:$N$100)

 

[nemmi69]

I did this, twice, using a different method each time, and manually spot-checked afterward. In both cases values were consistently identical - 5 digit zip codes using the zip code special format with no leading or trailing spaces. See the third paragraph of my post to see how I did this.

What I did notice was that where as Excel would still treat the search cells as values even though set as text it did not do the same for "zipcode". Might be best to set them all as values.