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)

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Low[/TD]
[TD]High[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12000[/TD]
[TD]12999[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Serial Number[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0001234AB74C[/TD]
[TD]Modem
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0001234EG89L[/TD]
[TD]Modem[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]123456[/TD]
[TD]Transmitter[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]789123[/TD]
[TD]Server[/TD]
[/TR]
</tbody>[/TABLE]










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

[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100000[/TD]
[TD]699999[/TD]
[TD]Transmitter[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]700000[/TD]
[TD]999999[/TD]
[TD]Server[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD]Modem[/TD]
[/TR]
</tbody>[/TABLE]

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?