Lookup/Find Prefixes in Range

**slpgma** · 07-13-2012, 03:15 PM

I have the two tables below.

Prefixes

PN

SN

DN

Values	Valid
PN001	T
PA123	F
SN654	T

I am trying to determine if my value in the second table has the proper prefix in it which are defined in the first table and if so then indicate that the value is valid. I have looked at the FIND, VLOOKUP, MATCH, etc to see if I can find something to work and I am drawing a complete blank. Basically I was hoping for something like this =FIND(B3:B5,B11), but that would have been too easy.

Is there a way that I can do this using a formula?

Thanks

**Richard Buttrey** · 07-13-2012, 03:26 PM

Presumably something like

=IF(MATCH(LEFT(B11,2),B3:B5,FALSE),"T","F")

**slpgma** · 07-13-2012, 05:49 PM

Thanks for the quick response. This will help in most cases but some of my prefixes will be longer than 2 characters. Do you think it is possible to handle this condition?

**Richard Buttrey** · 07-13-2012, 07:20 PM

One way if there may be different lengths of alpha characters is

=IF(ISERROR(MATCH(LEFT(B11,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},B11)),"",FIND({1;2;3;4;5;6;7;8;9;0},B11)))-1),B3:B5,FALSE)),"F","T")