Lookup multiple columns and return multiple values of rows that have data

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Is it possible to look up within multiple columns and return the rows that have data if that column is selected in another field?

Example data would be something like columns c1-z1 having values 1-24. If rows 2-100 had 'x' or any other value indicating they fit that column, would it be possible to have sheet 2 cell a1 have a drop down menu of 1-24 and whichever it selects bring up the rows that actually fit that selected column?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
Hi Muhleebbin,

Here is the data sheet1 (with columns 6 to 24 and rows 10 to 197 hidden for brevity).

Muhleebbin.xlsx
CDEFG
112345
2
3xx
4
5x
6xx
7x
8x
9x
198xxxxx
199x
200x
Sheet1


Here's the selection in Sheet2 (note that column C formula should be copied down to row 200)

Muhleebbin.xlsx
ABC
1ColumnRow
253
36
48
59
6198
7199
8200
9 
10 
Sheet2
Cell Formulas
RangeFormula
C2:C10C2=IFERROR(AGGREGATE(15,6,ROW(Sheet1!$C$2:$Z$200)/((Sheet1!$C$2:$Z$200="x")*(COLUMN(Sheet1!$C$2:$Z$200)=MATCH($A$2,Sheet1!$C$1:$Z$1,0)+COLUMN(Sheet1!$B$1))),ROW()-ROW($C$1)),"")
Cells with Data Validation
CellAllowCriteria
A2List=Sheet1!$C$1:$Z$1
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi Toadstool,

Thank you very much! That worked perfectly!

If I were to populate the data with lettering instead of 'x', would this still be possible? If the column titles are countries and I populated with S=self, Fa=Family or Fr=Friends rather than just 'x', that would eliminate many columns and help immensely. I believe there will be those options plus '+' or '-' indicating interest or lackthereof and possibly some new ones as I continue this project. Ultimately it would look in the column selected and then bring up any row that has a populated value. I could then use an index/match to find the actual value.

I apologize for the change but your formula just gave me this secondary idea.
 
Last edited:

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
Yes, it's a straightforward change.

Here's the modified data in Sheet1 but I'm just showing a few rows and columns.

Muhleebbin2.xlsx
CDEFGHI
1FranceSpainBelgiumUKUSAItalyGreece
2
3SS+
4S
5Fa-Fa
6FrFa+
7S-
8Fr+
9SFa+S-Fa-
198
Sheet1


Here's the modified Sheet2

Muhleebbin2.xlsx
ABCD
1ColumnRowContent
2USA3S+
36Fa+
48Fr+
5  
6  
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=IFERROR(AGGREGATE(15,6,ROW(Sheet1!$C$2:$Z$200)/((Sheet1!$C$2:$Z$200<>"")*(COLUMN(Sheet1!$C$2:$Z$200)=MATCH($A$2,Sheet1!$C$1:$Z$1,0)+COLUMN(Sheet1!$B$1))),ROW()-ROW($C$1)),"")
D2:D6D2=IFERROR(INDEX(Sheet1!$C$2:$Z$200,C2-ROW(Sheet1!$C$1),MATCH($A$2,Sheet1!$C$1:$Z$1,0)),"")
Cells with Data Validation
CellAllowCriteria
A2List=Sheet1!$C$1:$Z$1
 
Solution

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Thank you very much for your help here! It works just as needed!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,747
Messages
5,638,118
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top