# LookUp AlphaNumeric Values

#### trekover

##### New Member
Hi there,

I hope some one can help.

Basically we have a sheet on our excel file with part numbers in column a and then corresponding image file names in column b.

On a separate sheet in the same excel file we have over 90,000 products.

What we would like to do is create a formula that can search for the part number on the second sheet and then input the image file name next to it from the other sheet.

I was successful in doing this HOWEVER the formula I used only worked on part numbers which were only numbers and approximately 90% of our 90,000 products are alphanumeric ie DA1991. Can anyone help with writing the formula which would locate the correct part number for each file name for both numeric part numbers and alpha numeric part numbers?

Any help is appreciated.

Thanks

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
In sheet2 (sheet with 90000 products) you have this: DA1991
What do you have on sheet1 in column A?
Could you put the used formula?

Thank you for getting back to me.

Sorry my mistake its as follows;

In sheet1 with all the products I have the Part No in column A (Part No) ie Da1991, 137564, LR0012199, RTC3475 etc

In sheet2 with the filenames, column A is again the Part No, whilst column B (Image Source) is the relative filenames ie https://cdn.shopify.com/s/files/1/0889/4632/files/xbd100670-land-rover-defender-front-clear-indicator-flasher-light-assmebly(2).gif

I have an array in sheet2 called "list2" that includes both columns A and B so both Part No's and File Names.

The formula I have written is as follows; =VLOOKUP(\$A2,list2,2,FALSE)

To get this to work I have had to use Text to Columns on column A (Part No) in both sheets otherwise it was just bringing back #N/A results for all rows.

The formula you have should work in both cases!

Does part no. Da1991 definitely appear in sheet2 as Da1991, or are you just looking for the Number part 1991?

Try

=VLOOKUP("*"&\$A2,list2,2,FALSE)

Realised one was Capitalised and one was not. Seems to work now. Thank you though.

You wouldn't happen know if there is a way of me getting more than one result for a Part No?

Some Parts I have numerous images for therefore numerous filenames could be returned. At the minute I am getting one returned. Ideally if they could all be returned that would be fantastic but I have no idea where to start with that?

If not, no worries will deal with what we have got.

Thanks.

Just keep in mind, it's always best to display the exact data showing the expected results. It can help the members with their reply. It might be beneficial for you to post a sample of your data for viewing purposes.

In this case it would be good if you put an example of sheet 1 and another example of sheet 2

Replies
2
Views
375
Replies
4
Views
110
Replies
1
Views
182
Replies
7
Views
104
Replies
1
Views
173

1,206,713
Messages
6,074,483
Members
446,071
Latest member
gaborfreeman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back