Find related items based on multiple criteria > index+match multiple criteria or somthing else?

lvanderpol

New Member
Joined
Oct 14, 2019
Messages
1
Hi all,

This is a challenge, at least for me. What I'm trying to achieve is the following: I've a big list of items with several specs. I want to project the matching items (with the same specs) in columns after the item to match.

Simplified example of the data:

ItemSpec 1Spec 2Spec 3Spec 4
car1020ABCDEF
bal1030ABCGHI
bike1020ABCDEF
house2030JKLMNO
table1030ABCGHI
motor1020ABCDEF

<tbody>
</tbody>







Result wished for:

ItemRelated 1Related 2Related 3Related 4
carbikemotor
baltable
bikecarmotor
house
tablebal
motorcarbike

<tbody>
</tbody>







Is this possible at all? I tried and combined several index matches found on the forum, but non of them worked for this scenario. I really hope someone has a working solution, as I have thousands of rows and a couple thousand combinations I guess.

Many thanks in advance!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You could ensure your specifications in a given column had the same character length then concatenate all columns with dashes into a single column. Index match with the combined column would show matches.
Another method would be using a filter to show only rows where the all of the filtered columns matched the inputs.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top