Reputation: 35
I have an Excel sheet with two tables (like actual tables). One table lists App requirements and other shows Hardware requirements.
Example
A | B | C | D | |
---|---|---|---|---|
1 | Apps | Software Dev | Web Dev | Games Dev |
2 | Word | x | ||
3 | Powerpoint | x | ||
4 | Excel | x | x | |
5 | Outlook | x | x |
A | B | C | D | |
---|---|---|---|---|
7 | Hardware | Software Dev | Web Dev | Games Dev |
8 | Laptop | x | x | |
9 | Desktop | x | x | x |
10 | Mobile | x |
I have a cell where I input Job Title (e.g. Software Dev). I can't figure out VLOOKUPS to get my desired output of all the Apps and Hardware.
Enter Job Title | Software Dev |
---|---|
Excel | |
Desktop | |
Mobile |
I would like the output to also have the side headers like "Apps" and "Hardware" but I would like to figure this one out first.
Upvotes: 0
Views: 3907
Reputation: 14184
If you have Excel 365, you can get this result by applying two FILTER
functions and then joining both spill ranges (as described in this Reddit post). Combined into one formula, you get:
=LET(filter1,ROWS(FILTER(Apps[Apps],INDIRECT("Apps"&"["&Selection&"]")="x")),filter2,ROWS(FILTER(Hardware[Hardware],INDIRECT("Hardware"&"["&Selection&"]")="x")),seq,SEQUENCE(filter1+filter2),IF(seq<=filter1,INDEX(FILTER(Apps[Apps],INDIRECT("Apps"&"["&Selection&"]")="x"),seq),INDEX(FILTER(Hardware[Hardware],INDIRECT("Hardware"&"["&Selection&"]")="x"),seq-filter1)))
In this formula, the first table is called Apps
, the second Hardware
. Selection
is the named range for the cell where I made a dropdown with your three options.
To get the linked "category" in adjacent cells, I've simply used INDEX/MATCH
. I've entered above-mentioned formula in H3
, so:
=IFERROR(IFERROR(IF(MATCH(H3,Apps[Apps],0)>0,"Apps"),IF(MATCH(H3,Hardware[Hardware],0)>0,"Hardware")),"")
This one you should drag down to the max of your spilled results (or beyond that, since the outer IFERROR
gets you BLANK
in case of no result).
Result:
Upvotes: 1
Reputation: 2800
Alternate answer by using array formulas
You may use array formulas for this purpose combined with some tricks to make it work, if you do not wish to alter original data and just work outside of it.
By using your example data (I changed the column on purpose for second table to demonstrate that they can be in different columns, or sheets). This was based on this example
Character to spot requirements | Job Title | Column found in Table 1 | Column found in Table 1 |
---|---|---|---|
x | Software Dev | =SUBSTITUTE(ADDRESS(1,MATCH(N4,A5:D5,0),4),1,"") | =SUBSTITUTE(ADDRESS(1,MATCH(N4,F5:I5,0)+5,4),1,"") |
Columns for results | =SUBSTITUTE(ADDRESS(1,MATCH("Apps",A5:D5,0),4),1,"") | =SUBSTITUTE(ADDRESS(1,MATCH("Hardware",F5:I5,0)+5,4),1,"") | |
RowTableStarts | 3 | 3 | |
RowTableStarts | 10 | 10 |
Then on a separate row get the array formula as this one (one for apps and one for hardware)
In my image this is for apps
=INDEX(INDIRECT("$"&$O$5&"$"&$O$6&":$"&$O$5&"$"&$O$7), SMALL(IF(ISNUMBER(MATCH(INDIRECT("$"&$O$4&"$"&$O$6&":$"&$O$4&"$"&$O$7), $M$4, 0)), MATCH(ROW(INDIRECT("$"&$O$4&"$"&$O$6&":$"&$O$4&"$"&$O$7)), ROW(INDIRECT("$"&$O$4&"$"&$O$6&":$"&$O$4&"$"&$O$7))), ""), ROWS($A$1:A1)))
And this is for hardware
=INDEX(INDIRECT("$"&$P$5&"$"&$P$6&":$"&$P$5&"$"&$P$7), SMALL(IF(ISNUMBER(MATCH(INDIRECT("$"&$P$4&"$"&$P$6&":$"&$P$4&"$"&$P$7), $M$4, 0)), MATCH(ROW(INDIRECT("$"&$P$4&"$"&$P$6&":$"&$P$4&"$"&$P$7)), ROW(INDIRECT("$"&$P$4&"$"&$P$6&":$"&$P$4&"$"&$P$7))), ""), ROWS($A$1:A1)))
Extend the formula and get the results desired
Example working formulas
Although a VBA solution may be better if you really need to keep table formats (either build a dummy one that mixes them or loop through each one of them and append the results)
Upvotes: 1
Reputation: 1059
You should rethink how your data is laid out to make querying simpler. The unique thing (unique id for the record, key for the row) would be the job title. Everything else is based on the job title and would therefore be columns. Instead of using "x" to designate whether or not a particular job should be assigned a particular piece of hardware or software use boolean logic "True" or a value of "1".
I also made the above a smart table (select all of the table cells, go to the "Insert" tab, select "Table", make sure "my table has headers" is checked). I named it tblJobs under "Table Design" so that formulas would look cleaner. For the lookup table I've limited user input using a data validation drop down list ("Data" tab -> "Data Validation") so that they can't type garbage in the field. Otherwise they're going to type things wrong and complain about how it "doesn't work" when really they "can't type."
The formula below in O2 was copied down to the rest and is for whether or not the particular job should be assigned the specific hardware or Software:
=IF(INDEX(INDIRECT("tblJobs[" & N2 & "]"), MATCH($O$1, tblJobs[Job Title], FALSE))=1, TRUE, FALSE)
All of the formulas reference $O$1 so that when you select a different job from the drop down validation list all of the cells update based on the selected job. Finally, if you wanted you could add a filter to columns N and O, and only show "True" values.
If you were going to keep the spreadsheet the same, I would create an ADDRESS() reference and then use INDIRECT() to return the contents of the address reference.
Where G2 = =ADDRESS(MATCH(F2, A:A, FALSE), MATCH($G$1,A$1:D$1,FALSE))
Then wrap in an indirect: =INDIRECT(ADDRESS(MATCH(F2, A:A, FALSE), MATCH($G$1,A$1:D$1,FALSE)))
Upvotes: 1