Lookup query/match for multiple columns to return multiple values

[Yagnivek]

I’m not sure if Calc can do this, but I’ll explain it as best I can.

I have Column A of Names. Column B, C and D contain data to be searched. This data is from a static list of 20 values. For now I’ll use integers from 1 to 20 for this data. However, the actual data is a static list of strings. i.e. no regex/wildcard pattern matching needed.

Example Sheet:

A             B             C             D

Joe         5             13             10

John       4             10             7

Jane       16            5             18

Mary       10            7              4            

Now, I create a cell on a different sheet or same sheet, doesn’t matter. This cell is a dropdown of the values 1-20.
I want to select a value, lets say 10. I then want the cells below the selected value to display Joe, John and Mary, but not Jane since she does not have 10 in her row, with each in their own row in the column, not concatenated together in one cell.

Like:

     A

1 10

2 Joe

3 John

4 Mary

Where A1 is the selected value from the dropdown.

Now, the data cannot be sorted so all matching data is in the same column, the position of the original data is important and can’t be moved, but the results are not. For example, I do not need to know which column Joe has a value of 10 in, only that Joe has a 10, i.e. his name is on the new list, but I can’t move the 10 value to column B from D.

I have a feeling the answer will be: put it all in Base and query from there with data-sources.

[Lupp]

Finding the matching datasets (rows) is one thing and it’s not complicated. Getting them (or the “KEY” only) shown in a contiguous list (compacted) is another thing and which solution to suggest will depend on some conditions, mainly if there are expectations concerning further enhancements, scalability and maintainability. There are solutions needing a little redsign every once in a while.

I would strongly recommend to use rather short formulae and some helper columns. How you can do it using MATCH() and INDEX() or OFFSET() is demonstrated in the attached example ask43588SelectMultiColORcondition001.ods.

Showing the results sorted can also be done. It will require a second engine of some helper columns.

[Yagnivek]

Thanks for the help, does exactly what I was needing, now to hide all those helper columns on another sheet.