Search string aginst multiple criteria and vlookup to provide value

Attached I have a table where I am trying to determine the manufacturer and model number based on the serial number of a device. Thanks to a different thread I have the basics working. However, my vlookup only checks the first 4 characters of a serial number for a match. I have have a rule set that depicts how each serial number can be translated to manufacturer and model. Since each manufacturer uses different syntax for their serial number there can be many options. The one cell where a serial number is input into my sheet must be able to search through multiple sets of criteria. For example, I need to vlookup and match the 1st 4 characters of a serial AND/OR I need to look at the last 2 characters AND/OR look at the first 2 characters....etc (based on all the different types of equipment I have).

I have a working sheet but as you can see, I am not sure how to add multiple sets of vlookups to account for the various different possible serial numbers. Any advice?

Asset Detection (1).xlsx

Hi guys,

That was a really elegant solution from micope21 . I was beavering away on another solution (attached) which involved several hidden "helper columns".

Asset Detection for smidgen11.xlsx

I decided to copy micope21's formula into my model, and found I was getting a different result. This was caused by the 12 at the start of the Creston machine. micope21's solution was to change the lookup list on the Series.Rules sheet to 4 characters.

I am not clever enough to suggest a solution if this is not acceptable, so I attach the way I did it! (Note: I had to ensure that the "Looking for" column was formatted as TEXT.

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

This is helpful, I really appreciate it. However, since devices made by Zelda have the same suffix "ZZ", the prefix of their serial numbers will always be unique so I don't think I can use 12A in the Looking for column. Example serial numbers for Zelda devices could be:
12ABC123ZZ -> works with current method
5400S239ZZ -> resolves to NEC V802
11111WK2ZZ -> works in current method
...etc

Is there a way to account for searching both the first 4 characters and\or at the same time look at the last 2 characters?

I cant seem to get this lookup to work in Google Sheets although it accepts it. Anyone have experience with Sheet with ideas?

How do I differentiate for the serial number that I know will always begin with 2 characters from a manufacturers and wont know the rest. Also, the serial numbers that I will only ever know the last few charters. Would I need some type of long nested if?

I have a tab that does specify the serial number rules, however, since different manufacturers have different rulesets I need to account for all senarios. Most manufactures can be determined by their first few characters (some can be identified by the first 4 characters then the rest are completely random, some can be identified by only the first 2 characters and then the rest of the characters are random). The kicker is the manufacturers that can only be identified by the last few characters because their prefixes are the random characters (see Row 14 on Serial.Rules). Im probably going over my head here trying to find a way (maybe it cant be done due to complexity) but I was hoping there was away to account for all scenarios.

Correct, they can be complex. I do however have the rulesets for most of my equipment given to me by manufacturers. For simplicity, I am trying to create an asset detection sheet using prefixes and suffixes.

Could i use some type of nested if that says if no match, try using the suffix, if nothing then definitely no match?