Vlookup Not working
- Thread starter Shabbo
- Start date
PCosta87
Well-Known Member
Hi,
The problem is that column G is formatted as text...
In fact, the formula in H is also not returning a result due to this.
If you edit G3 in the "523" sheet you will see excel prompting you to convert to number...
For the entire column you can use text to columns to fix that.
EDIT: if you replace the lookup value 1121542464 with '1121542464 (notice the ') it will also work as it "converts" the number to text.
Hope this helps
The problem is that column G is formatted as text...
In fact, the formula in H is also not returning a result due to this.
If you edit G3 in the "523" sheet you will see excel prompting you to convert to number...
For the entire column you can use text to columns to fix that.
EDIT: if you replace the lookup value 1121542464 with '1121542464 (notice the ') it will also work as it "converts" the number to text.
Hope this helps
Last edited:
I Tried Its not working sir.
PCosta87
Well-Known Member
Hi,
I just downloaded to test that and it opened without any problems...
Please double check.
Thanks
NARAYANK991
Excel Ninja
Hi ,
Just to add to what ever has already been posted.
When ever you see a lookup value which looks identical to what is present in the lookup range , the problems can be several :
1. Leading / trailing spaces or special characters which are not displayed e.g. characters with the ASCII code 160.
2. Problems with the data type , as already pointed out.
To verify this , the simplest way is to use the TYPE function ; if you use this on the data and the lookup range , as in :
=TYPE(G3)
=TYPE(I3)
you will see that the former returns the value 2 , meaning a TEXT value , while the latter returns the value 1 , meaning a NUMBER.
When you wish to use a VLOOKUP function to look up such data whose type is incompatible with the data in the lookup range , the simplest way is to coerce the lookup value type to the type of the data in the lookup range.
Suppose we have the starting formula of :
=VLOOKUP(I3,'523'!$G:$G,1,0)
1. If I3 is numeric , and the data in column G is text , then change the above formula to :
=VLOOKUP(I3&"",'523'!$G:$G,1,0)
The highlighted portion converts a numeric value to text , without compromising on anything.
2. If I3 is text , and the data in column G is numeric , then change the original formula to :
=VLOOKUP(I3+0,'523'!$G:$G,1,0)
The highlighted portion converts a text value to numeric , without compromising on anything.
Narayan
Just to add to what ever has already been posted.
When ever you see a lookup value which looks identical to what is present in the lookup range , the problems can be several :
1. Leading / trailing spaces or special characters which are not displayed e.g. characters with the ASCII code 160.
2. Problems with the data type , as already pointed out.
To verify this , the simplest way is to use the TYPE function ; if you use this on the data and the lookup range , as in :
=TYPE(G3)
=TYPE(I3)
you will see that the former returns the value 2 , meaning a TEXT value , while the latter returns the value 1 , meaning a NUMBER.
When you wish to use a VLOOKUP function to look up such data whose type is incompatible with the data in the lookup range , the simplest way is to coerce the lookup value type to the type of the data in the lookup range.
Suppose we have the starting formula of :
=VLOOKUP(I3,'523'!$G:$G,1,0)
1. If I3 is numeric , and the data in column G is text , then change the above formula to :
=VLOOKUP(I3&"",'523'!$G:$G,1,0)
The highlighted portion converts a numeric value to text , without compromising on anything.
2. If I3 is text , and the data in column G is numeric , then change the original formula to :
=VLOOKUP(I3+0,'523'!$G:$G,1,0)
The highlighted portion converts a text value to numeric , without compromising on anything.
Narayan