Conditional Vlookup - Cherry picking information from the table ar

G

Guest

I have managed to figure out how to use the Vlookup command to find
information in a column and to return information using the following
formula:

In this example, I find two like values and return some data or a message -
=IF(ISNA(VLOOKUP(E2,\$B\$2:\$E\$1957,3,FALSE))=TRUE, "Product Number is not
found", VLOOKUP(E2,\$B\$2:\$E\$1957,3,FALSE))

However, I need to understand how to find T1234 in list that contains
T1234_TEST, I want the lookup command to match the "T1234" portion of the
project name. Is there some way to key on the first 5 characters and
consider this a match? In the earlier example, it was easy to find the value
with the exact name - this is a bit more tricky..

FACTS:
Lookup Value = T1234
Table Array = T1234_Test
The problem is the N/A is return and I need it to be smart enough to say,
yes we've matched the first 5 characters in T1234_Test.

D

Dave Peterson

=vlookup(e2&"*",\$b\$2:\$e\$1957,3,false)

or maybe

=vlookup(left(e2,5)&"*",\$b\$2:\$e\$1957,3,false)

R

RagDyer

First of all, you *don't* need the " =TRUE " in your formula.

Next ... is the " T1234 " in E2... or do you intend to "hard code" it into
the formula itself?

If it's in cell E2, try this:

=IF(ISNA(VLOOKUP(E2&"*",\$B\$2:\$E\$1957,3,0)),"Product Number is not
found",VLOOKUP(E2&"*",\$B\$2:\$E\$1957,3,0))

If you're going to hard code it, try this:

=IF(ISNA(VLOOKUP("T1234*",\$B\$2:\$E\$1957,3,0)),"Product Number is not
found",VLOOKUP(E2&"*",\$B\$2:\$E\$1957,3,0))

R

RagDyer

That second formula needs to be changed to this:

=IF(ISNA(VLOOKUP("T1234*",\$B\$2:\$E\$1957,3,0)),"Product Number is not
found",VLOOKUP("T1234*",\$B\$2:\$E\$1957,3,0))

G

Guest

Change your "false" to true, or leave it blank. Then sort your look up table
by column 1.

G

Guest

Another fuzzy match play to try ..

Place in say, F2, then array-enter the formula by pressing CTRL+SHIFT+ENTER
=IF(LEN(E2)=0,"",IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(TRIM(E2),TRIM(\$B\$2:\$B\$1957))),0)),"Product
Number is not
found",INDEX(\$D\$2:\$D\$1957,MATCH(TRUE,ISNUMBER(SEARCH(TRIM(E2),TRIM(\$B\$2:\$B\$1957))),0))))
Copy F2 down

D

Dave Peterson

I think the wild card suggestion will work.
Change your "false" to true, or leave it blank. Then sort your look up table
by column 1.