I'm trying to match a list of words from Column A against another list of words in column B. I only need the first three characters of the words in column A to match the first three characters of the words in column B. I would like the result to be the same value in column B. Is this possible?
Here is what I'm using: =VLOOKUP(LEFT(A1,3),B$2:B$22, 2,TRUE)
But, I keep getting #N/A. Can anyone help?
=IF(ISNUMBER(SEARCH(LEFT(H2,3),I2)),"YES","NO")))
=VLOOKUP(LEFT(A4,LEN(A4)-9),$D:$F,3,0)
I use this if my Lookup_Value needs to be truncated because of the format the name is in the Table_Array. E.g. my Lookup_Value is "Eastbay District", but the Table_Array list I have only shows this as "Eastbay". "Eastbay District" minus 9 characters will result in "Eastbay".
I hope this helps!
=VLOOKUP(LEFT(A1,3),LEFT(B$2:B$22,3), 1,FALSE)
LEFT() truncates the first n character of a string, and you need to do it in both columns. The third parameter of VLOOKUP is the number of the column to return with. So if your range is not only B$2:B$22 but B$2:C$22 you can choose to return with column B value (1) or column C value (2)
=VLOOKUP(Left(A1,1),B$2:B$22,2,FALSE)
Left is because you are starting the word/alphanumeric text from the left. the number "1" which i have placed is after lookup value in this case "A1" is because my search includes the formula for 1st character. If second character is asked it would be (A1,2) quite simple really :)
Something neat...
I wanted to look up an "Exact Town ID" based on a "Partial Exact Town Name" BUT although I had the entire town name I was searching against a list of Partial town names. So I First found the "Exact Town Name" based on the part (which was actually a partial name since the "master list" is partial names unfortunately)... THEN searched for the "Exact Town ID" based on that Exact town name SO all my Vlookups/Index/Match-whatevers....were set to EXACT ....
=INDEX(county_cheatsheet!$E$1:$E$516,MATCH(VLOOKUP(LEFT(D3,3)&"*",county_cheatsheet!$E$1:$E$516,1,FALSE),county_cheatsheet!$E$1:E$516,0))
The lookup of the "first three letters of the partial town name against the list of partial town names is the MATCH(VLOOKUP(LEFT(D3,3)&"*" part
Try using a wildcard like this
=VLOOKUP(LEFT(A1,3)&"*",B$2:B$22,1,FALSE)
so if A1 is "barry" that formula will return the first value in B2:B22 that starts with "bar"
Source: Stackoverflow.com