I have a simple example here. I have an excel sheet with two tables (like actual tables). One table lists App requirements and other shows Hardware requirements
| 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 can input in Job Title (for eg Software Dev) and i cant for the life of me figure out VLOOKUPS to get me my desired output of showing me all the Apps and Hardware. Am i missing a really simple solution?
| Enter Job Title | Software Dev |
|---|---|
| Excel | |
| Desktop | |
| Mobile |
Ideally id like the output to also have the side headers like "Apps" and "Hardware" but id like to figure this one out first
Thanks
CodePudding user response：
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 
| 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)
CodePudding user response：
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 
CodePudding user response：
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:
- O2:
=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)))
- Finds the row by searching down A:A using MATCH() for each thing you're looking for for each job.
- Finds the column by searching A1:D1 using MATCH() for each job you're referencing.
- Combine the row and column in an ADDRESS() function in the format ADDRESS(row, column) and it returns an address reference like $B$4.
- INDIRECT() then consumes the address reference and returns what exists in that location.
