Lookup / Index Match on more than one column

[victoria2207]

Good Afternoon,

I have to perform an index match to try and map all the different codes assigned to one item to the new final item.

So I have the following:

[TABLE="width: 370"]
<colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4209;"> <col width="137" style="width: 103pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="146" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5205;"> <tbody>[TR]
[TD="width: 91, bgcolor: transparent"][TABLE="width: 370"]
<colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4209;"> <col width="137" style="width: 103pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="146" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5205;"> <tbody>[TR]
[TD="width: 91, bgcolor: transparent"]Old Item Code[/TD]
[TD="width: 118, bgcolor: transparent"]Supplier Item Code[/TD]
[TD="width: 137, bgcolor: transparent"]Alternative Item Code[/TD]
[TD="width: 146, bgcolor: transparent"]NEW (FINAL) Item code[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1234[/TD]
[TD="bgcolor: transparent"]AB1234[/TD]
[TD="bgcolor: transparent"]AB 1234[/TD]
[TD="bgcolor: transparent"]ABC1234[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 118, bgcolor: transparent"][/TD]
[TD="width: 137, bgcolor: transparent"][/TD]
[TD="width: 146, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


I have a large amounts of items and a large amount of documents that will have variations on all of the different item codes.

I am going to try and add the NEW Item code to all documents to tie them all together.

I've seen lots of forum answers with regards to index match where there are 2 columns that both must be considered but can we look up to 3 columns where it might only be one of them and return the right value?

Thank you in advance,


Vic2207

 

[sheetspread]

Are you trying to look up one or more exact matches from three+ possible choices? Or the closest match which would require something like:

https://www.mrexcel.com/forum/excel...zy-matching-new-version-plus-explanation.html

 

[victoria2207]

Thanks Spreadsheet - I'll have a bash.

My initial thought is I'm not keen on putting this code in every sheet I want to do a lookup on as I'm not sure of the implications, ie size of files/ etc

I guess I could always enter it to get the values then past values and delete the code though this might take longer than just doing 3 different lookups?

I just thought there might be an easier way.

Thank you for your response.

 

[sheetspread]

You can save it to a single workbook and run on different sheets:

https://www.mrexcel.com/forum/excel-questions/360789-saving-vba-function-personal-workbook.html

 

[victoria2207]

Ahh - Of course - I have 2 codes in there I use to run 2 personalised buttons on my ribbon too!!! Thank you.