Vlookup returns #NA, when table array is valid

[questforexcel]

Hi All,

So my vlookup formula is referencing from a table where data exists and I am pulling it from the right column.

There are no spaces , or additional characters in any cell.

What I understand is the issue is that the lookup range and table array may still be in different formats. I read somewhere, that using text to columns to convert both the formats to general should help. I even did that, that did not work.

This is my formula.

=VLOOKUP(LEFT(A5,5),'Fedex Chart'!$B$4:$E$91252,4,0)

The table array looks like this

[TABLE="width: 333"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] Dest Zip/Postal Code[/TD]
[TD]Post Code[/TD]
[TD] Service Days[/TD]
[TD] Zone[/TD]
[/TR]
[TR]
[TD="align: right"]500.00[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]501.00[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]502.00[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]503.00[/TD]
[TD="align: right"]503[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]504.00[/TD]
[TD="align: right"]504[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]505.00[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

My lookup range are

[TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Postal Code[/TD]
[/TR]
[TR]
[TD="align: right"]2176[/TD]
[/TR]
[TR]
[TD="align: right"]2635[/TD]
[/TR]
[TR]
[TD="align: right"]2816[/TD]
[/TR]
[TR]
[TD="align: right"]8505[/TD]
[/TR]
[TR]
[TD="align: right"]8826[/TD]
[/TR]
[TR]
[TD="align: right"]10598[/TD]
[/TR]
</tbody>[/TABLE]

I searched online for solutions to this problem, could not find an answer. Could you please help in.

Regards

 

[steve the fish]

LEFT produces text whether the result is a number or not so maybe:

=VLOOKUP(0+LEFT(A5,5),'Fedex Chart'!$B$4:$E$91252,4,0)

 

[Joe4]

Even if both the values you are matching on are General (Numbers) and not Text, the LEFT function you are using returns a String result. So comparing a String to a Number would not work.
Try changing:

[COLOR=#333333]LEFT(A5,5)

to
[/COLOR]

[COLOR=#333333]LEFT(A5,5)+0[/COLOR]

to coerce it to be a number.

 

[AlanY]

are you trying to pull data from

Book1
	A	B	C	D
1	Dest Zip/Postal Code	Post Code	Service Days	Zone
2	500	500	3	6
3	501	501	3	6
4	502	502	3	6
5	503	503	3	6
6	504	504	3	6
7	505	505	3	6
Sheet5

to

Book1
	F	G
1	Postal Code
2	2176	?
3	2635	?
4	2816	?
5	8505	?
6	8826	?
7	10598	?
Sheet5

if so, what results are you expecting?

 

[questforexcel]

Thank you so much for the quick response, this did work

Just a clarification, if you could help, I had another sheet of data with similar referencing points.

Over there, the "LEFT" function formula worked fine. I didn't have to "+0" and it worked. Is there something, I'd be missing there or would have added there.


This is how the formula appeared

=IFERROR(VLOOKUP(LEFT(A5,3),'USPS Chart'!$B$5:$C$123,2,1),"")

This was the lookup array

[TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Postal Code[/TD]
[/TR]
[TR]
[TD="align: right"]2575[/TD]
[/TR]
[TR]
[TD="align: right"]7632[/TD]
[/TR]
[TR]
[TD="align: right"]9314[/TD]
[/TR]
[TR]
[TD="align: right"]9354[/TD]
[/TR]
[TR]
[TD="align: right"]10010[/TD]
[/TR]
</tbody>[/TABLE]

These were the array table where it was pulling the information from

[TABLE="width: 80"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ZIP Code[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD]005-076[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]078-079[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]080-087[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]088-089[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]090-098[/TD]
[TD]6+[/TD]
[/TR]
[TR]
[TD]100-149[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]150-166[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]167[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]168-212[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much for the quick help.

Even if both the values you are matching on are General (Numbers) and not Text, the LEFT function you are using returns a String result. So comparing a String to a Number would not work.
Try changing:

[COLOR=#333333]LEFT(A5,5)[/COLOR]

to

[COLOR=#333333]LEFT(A5,5)+0[/COLOR]

to coerce it to be a number.

 

[questforexcel]

Hi,

Thats correct this is from where I am trying to pull my data.

2176 doesn't appear below as I'd only included the 1st few data points, the list goes on for 91K lines.

For 2176 i'd like it to return the corresponding zone value which is 6.

Also how did you insert these tables with row and column nos. Its so much easier for readers reference. Couldn't find this while typing out my queries.

are you trying to pull data from

	A	B	C	D
Dest Zip/Postal Code	Post Code	Service Days	Zone

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]501[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]503[/TD]
[TD="align: right"]503[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]504[/TD]
[TD="align: right"]504[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

</tbody>

Sheet5​

to

	F	G
Postal Code
?
?
?
?
?
?

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2176[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2635[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2816[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8505[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8826[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]10598[/TD]

</tbody>

Sheet5​

if so, what results are you expecting?

 

[Joe4]

Over there, the "LEFT" function formula worked fine. I didn't have to "+0" and it worked. Is there something, I'd be missing there or would have added there.

If the LEFT function worked over there, it means that the data column that you are matching on must be formatted as Text, not Numeric/General.

Basically, the value you are looking up and the value you are matching on MUST be the same data type (number-to-number or text-to-text).
Beware of "numbers" entered as "text". Switching the format to "General" will not change that. Formatting only affects how numbers are displayed. They do not change Data Types. You would need to use a method like Text to Columns to change that (or some formula).

 

[steve the fish]

In your first example you used:

=VLOOKUP(LEFT(A5,5),'Fedex Chart'!$B$4:$E$91252,4,0)

Then you used:

=IFERROR(VLOOKUP(LEFT(A5,3),'USPS Chart'!$B$5:$C$123,2,1),"")

There is a big difference between using ,0 and ,1 for the last parameter of a VLOOKUP. I suspect your 2nd example was producing results that arent exactly what you expect.

 

[questforexcel]

Thats correct, I had used 1 cause I wanted an approximation for my results.

My table array was in ranges, so I used 0, it did not return the values for my data.

When I used, 1, it did give in the approximate values to it.

Is this wrong? Is there a better way to reference data which falls in ranges.

Thank you

In your first example you used:

=VLOOKUP(LEFT(A5,5),'Fedex Chart'!$B$4:$E$91252,4,0)

Then you used:

=IFERROR(VLOOKUP(LEFT(A5,3),'USPS Chart'!$B$5:$C$123,2,1),"")

There is a big difference between using ,0 and ,1 for the last parameter of a VLOOKUP. I suspect your 2nd example was producing results that arent exactly what you expect.