LookUp AlphaNumeric Values

[trekover]

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

 

[DanteAmor]

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?

 

[trekover]

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.​

 

[gaz_chops]

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?

 

[gaz_chops]

Try

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

 

[trekover]

Thank you for your help.

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

 

[gaz_chops]

You're welcome, glad it's sorted.

 

[trekover]

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.

 

[DanteAmor]

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