Ask Your Question
0

Lookup query/match for multiple columns to return multiple values. [closed]

asked 2014-12-13 08:58:01 +0200

Yagnivek gravatar image

updated 2014-12-13 09:00:32 +0200

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-07 01:52:50.239880

1 Answer

Sort by » oldest newest most voted
0

answered 2014-12-13 16:00:20 +0200

Lupp gravatar image

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.

edit flag offensive delete link more

Comments

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

Yagnivek gravatar imageYagnivek ( 2014-12-13 19:08:10 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-12-13 08:58:01 +0200

Seen: 5,251 times

Last updated: Dec 13 '14