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

[lvanderpol]

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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Spec 1[/TD]
[TD]Spec 2[/TD]
[TD]Spec 3[/TD]
[TD]Spec 4[/TD]
[/TR]
[TR]
[TD]car[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]bal[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]ABC[/TD]
[TD]GHI[/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]house[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]JKL[/TD]
[TD]MNO[/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]ABC[/TD]
[TD]GHI[/TD]
[/TR]
[TR]
[TD]motor[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]







Result wished for:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Related 1[/TD]
[TD]Related 2[/TD]
[TD]Related 3[/TD]
[TD]Related 4[/TD]
[/TR]
[TR]
[TD]car[/TD]
[TD]bike[/TD]
[TD]motor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bal[/TD]
[TD]table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD]car[/TD]
[TD]motor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]house[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD]bal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]motor[/TD]
[TD]car[/TD]
[TD]bike[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







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!

 

[pbornemeier]

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.