VLook-Up Match first 3 characters of one column with another column

excel vlookup
user2364254 picture user2364254 · May 9, 2013 · Viewed 118.4k times · Source

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?

Answer

barry houdini picture barry houdini · May 9, 2013

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"

Related questions

Remove #N/A in vlookup result

How do I modify this function so that the result will merely be a blank cell rather than having #N/A show up if B2 has nothing in that cell? I think I might need something like an ISERROR check …

Read More
Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day. In a 2nd worksheet I have a list of …

Read More
Comparing two columns, and returning a specific adjacent cell in Excel

I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula. Comparing two columns for matches is easy enough. the tough part has been returning a specific cell once …

Read More