Conditional Vlookup - Cherry picking information from the table ar

[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..

Please help

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.

 

[Dave Peterson]

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

or maybe

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

 

[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))

 

[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))

 

[Guest]

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

anitadai

 

[Guest]

Another fuzzy match play to try ..

Place in say, F2, then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing 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

 

[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.

anitadai