Dev
Dev

Reputation: 35

How to return column names based on cell value in Excel using multiple tables?

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

Answers (3)

ouroboros1
ouroboros1

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:

enter image description here

Upvotes: 1

Sgdva
Sgdva

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

enter image description here

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

enter image description here

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

Lucretius
Lucretius

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".

enter image description here

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."

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

Upvotes: 1

Related Questions