Vlookup alphanumeric cells in a range

[Demona26]

Hello. I am trying to look up alphanumeric values against an alphanumeric range.
I can do this against an all-number range, but can't figure out the alphanumeric equivalent. The challenge is that, because it is alphanumeric, I can't define a clear range for Excel.

In my numeric lookup, if the value 12345 appears in cell A2, I can return the value "Yes" because the vlookup range is defined.

=vlookup(A2,D2:F2,3,true)

	D	E	F
1	Low	High	Value
2	12000	12999	Yes

<tbody>
</tbody>

In the alpha numeric version, I don't know what to put in the range, or how to have Excel find a partial match of an alphanumeric code.
An example of a value I am trying to find is: 0001234AD78D.
All of the alphanumeric values I want to find will start with 000123, and they (should) all be 12 characters long.

Suggestions?

 

[shknbk2]

Can you use an IF formula?

=IF(AND(A2>=D3, A2<=E3), F2,"")

 

[Demona26]

I could, but how do I define the alphanumeric range? The formula works fine with an all numeric number - I just don't know how to define the range for the alphanumeric lookup.

 

[shknbk2]

I need more info from your data. Can you provide a more-complete example of your data and what you're trying to do with it?

 

[Demona26]

Thank you for looking into this.

I have a spreadsheet of serial numbers. Some are all numeric and some are alphanumeric. I am using a vlookup range to identify the type of equipment based on the SN range.

The end result I'm looking for is as follows, where Vlookup is used in cells B2:B6 to find out what type of unit it is based on the serial number using the formula =Vlookup(A2,Sheet1!$J$1:$L$3,3,TRUE)

	A	B
1	Serial Number	Type
2	0001234AB74C	Modem
3	0001234EG89L	Modem
4	123456	Transmitter
5	789123	Server

<tbody>
</tbody>

My Reference page on Sheet 1 will look like this with the "?" indicating that I don't know what to enter

	J	K	L
1	100000	699999	Transmitter
2	700000	999999	Server
3	?	?	Modem

<tbody>
</tbody>

So the formula works fine with the numeric numbers as I can define a range for it to fall in. Lines 3 & 4 work fine.
I'm stuck on what to enter into cells J3 and K3 in order to find a range for Excel to look up the alphanumeric serial number in.

 

[shknbk2]

Use this formula in A2 instead of vlookup:

=INDEX($L$1:$L$3,MATCH(A2,$J$1:$J$3))

Then, J3 can be '000123 (make sure to include the apostrophe to ensure a text value). K3 can be blank unless you have modem ranges to further define subgroups.

 

[Demona26]

This kind of worked...it's still correctly picking up all the numeric values. But I have a serial number that is 0006398AEFKL and a different piece of equipment that starts with AST12345. The formula is identifying all of the AST serial numbers as modems, and I still get errors for all of the ones starting with 000639. Not sure what I am doing wrong?