Forum Discussion
A BASIC VLOOKUP PROBLEM
VLOOKUP doesnt work at B1(sheet3) in attached example
For example
If I write a letter and word combination like 4KK, the formula works
but it doenst work with just a number like 760
I tried to use the VALUE formula to solve this problem at column D but this time failed with combinations
Please help me if you know any solution of this
Here you are:
=IFERROR(VLOOKUP(A1*1,Sheet1!$A$1:$B$190,2,0),VLOOKUP(TEXT(A1,"0"),Sheet1!$A$1:$B$190,2,0))
You're covered for numbers and text.
4 Replies
Anıl Adaş the underlying issue here is that VLOOKUP expects (especially when you specify "FALSE" in the "range lookup" field) ....VLOOKUP expects an exact match. I underscored, bolded, and italicized "exact" because it really means exact.
So even if you've gotten things to the point where all the fields are clearly text, even if numeric in content, IF there were a trailing space--invisible to the eye, but there nonetheless--that can cause VLOOKUP to fail to find the match. And that trailing space could be in the table you're searching OR in the reference field.
So when VLOOKUP is used--and it's a very powerful function, very useful; I think I read recently that it's historically been the third most commonly used function--it's important to realize that it "thinks" like a computer. You and I might see 760, whether text or numerals, as the same, but the computer doesn't. You and I might see "key word" and "key word " as the same, but the computer doesn't.
Hope that helps get beneath the surface.
Here you are:
=IFERROR(VLOOKUP(A1*1,Sheet1!$A$1:$B$190,2,0),VLOOKUP(TEXT(A1,"0"),Sheet1!$A$1:$B$190,2,0))
You're covered for numbers and text.
If simple changing of format to "Text" does not work then try this:
On the Data Tools: Use "Text to columns" for column A of Sheet 1. On the third stem make sure that the format of the column is chosen as text. Finish. The formula on sheet3 will be free of errors.
Hope it helps.
