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

#### muhleebbin

##### Board Regular
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.

##### Well-known Member
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

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:

##### Well-known Member
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

#### muhleebbin

##### Board Regular
Thank you very much for your help here! It works just as needed!

Replies
2
Views
22
Replies
9
Views
85
Replies
2
Views
200
Replies
4
Views
176
Replies
2
Views
38

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.

### Which adblocker are you using?

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

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