vlookup returning #N/A and I have no idea why - need help please

[buroh]

Hi all,

I am getting annoyed by this and I cannot work out why the vlookup formula is returning #N/A when the value it is looking for exists.

the cells:

column C column D column E column F

lookup	result
1100101	#N/A​	1100101	Triple Bay
		1100201	Triple Bay
		1100301	Triple Bay
		1100401	Triple Bay
		1100500	Triple Bay
		1100501	Triple Bay

formula in D2

=VLOOKUP(C2,E2:F7,2,FALSE)

my formula; pretty simple right? The result I get below:

#N/A​

Column C is a formula result of extracting 1st 7 digits of number/text

Steps I took:

1. copied the formula result of Column C and paste it as value in another column and changed vlookup to look up that value - same result
2. copied it all to a new spreadsheet - same result
3. copied column E and F and paste it as values to another sheet; same result

I don't know what to do next, I am stuck on trying to solve this.

Wayne

 

[Peter_SSs]

I suspect that the formula result in C2 is text and the values in column E are numeric.

Try putting +0 on the end of the C2 formula. That should then return a number in C2 instead of text and I suspect that your VLOOKUP will then work.

If you don't want to actually change the C2 value, then another option is to try this formula in D2

=VLOOKUP(C2+0,E2:F7,2,FALSE)

 

[buroh]

I suspect that the formula result in C2 is text and the values in column E are numeric.

Try putting +0 on the end of the C2 formula. That should then return a number in C2 instead of text and I suspect that your VLOOKUP will then work.

If you don't want to actually change the C2 value, then another option is to try this formulka in d2

=VLOOKUP(C2+0,E2:F7,2,FALSE)

Thank you, that worked.

Weird, the format of the number has now changed, it was displaying off colour, I thought it was my screen, but as soon as I put the +0 in the formula, the colour was not off and formula worked.

So would a =LEFT formula produce the result in TEXT rather than General / Numeric?

 

[Peter_SSs]

So would a =LEFT formula produce the result in TEXT rather than General / Numeric?

Yes, the LEFT function returns Text

 

[buroh]

Yes, the LEFT function returns Text

Thank you,

I have used NUMBERVALUE in conjunction with my LEFT formula to produce a number result rather than text, thanks for your help.

 

[Peter_SSs]

I have used NUMBERVALUE in conjunction with my LEFT formula to produce a number result rather than text,

That is fine if you want to use that, but a simple +0 would do the same thing