VLOOKUP Error

[AlanH]

My dilemma: I have thousands of devices that only have one accurate way of telling me which make/model they are. Unfortunately, the prefix in each device # varies in length. Any way to manipulate the VLOOKUP function so that it will only look up the prefix to the actual number? Or is there another way that someone knows to help me categorize the devices?

Attached is an example of just 7 devices with their make/model specified. I did those manually, obviously. But I can't do that for thousands of devices...

Thanks in advance,
Alan

[khamilton]

Maybe something like this>
AlanH.xlsx

[martindwilson]

=VLOOKUP("31e*",$A$1:$B$7,2,FALSE) or
=VLOOKUP(d1&"*",$A$1:$B$7,2,FALSE) where d1 contains 31e

[NBVC]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[AlanH]

Maybe something like this>
Attachment 59739

That spreadsheet was actually returning the wrong results. A "343" device for example was saying it was a "TP I."

Attached is a spreadsheet which better shows my dillema.

Thanks again,
Alan

[khamilton]

I think this is working correctly>
AlanH.xlsx

[AlanH]

I think this is working correctly>
Attachment 59747

Wow, cool stuff. Looks to be working for me as well.

Thanks again for your help!

-Alan

[khamilton]

Your Welcome!

[AlanH]

Your Welcome!

I actually have a quick question about the formula if you wouldn't mind answering real quick:

=LOOKUP(2^15,SEARCH($F$2:$F$6,A2),$G$2:$G$6)

What does "2^15" do exactly? I put in "2^3" and it returned the same results?

[khamilton]

The 2^15 is one more number than the maximum number of characters allowed in a cell.

[AlanH]

Right on.

I should note that I added one thing to the formula (in bold):

=LOOKUP(2^6,SEARCH(Sheet5!$A$2:$A$6,LEFT(A66,5)),Sheet5!$B$2:$B$6)

If I didn't make it look up only the left 5 characters, it was actually going through the whole device number and if a 27 device had 34 in it, it would actually return the model as a TP II and not a CLASSIC.

Figured I would throw that in there!

Thanks again,
Alan