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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
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:

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,765
Members
416,136
Latest member
senthil_sk

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
Top