# Multiple Option Table Array

#### TKB

##### Board Regular
Hello,

So I have been trying to get this Array formula to work with multiple possibilities:

=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$P\$3, 1)<=\$P\$2)*(INDEX(tbl, , \$P\$3, 1)>=\$P\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(T3:\$T\$3)), , 1)

There are 2 ways I have tried to make this work with the "OR" function.

Attempt #1

=INDEX(tbl, SMALL(IF(OR((INDEX(tbl, , 1, 1)<="Plumbing")*(INDEX(tbl, , 1, 1)>="Plumbing"),(INDEX(tbl, , 1, 1)<="Plumbing,Electrical")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical"),(INDEX(tbl, , 1, 1)<="Plumbing,Interior")*(INDEX(tbl, , 1, 1)>="Plumbing,Interior"),(INDEX(tbl, , 1, 1)<="Plumbing,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Exterior"),(INDEX(tbl, , 1, 1)<="Plumbing,Electrical,Interior")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical,Interior"),(INDEX(tbl, , 1, 1)<="Plumbing,Electrical,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical,Exterior"),(INDEX(tbl, , 1, 1)<="Plumbing,Interior,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Interior,Exterior"),(INDEX(tbl, , 1, 1)<="Plumbing,Electrical,Interior,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical,Interior,Exterior")), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(T3:\$T\$3)), , 1)

Attempt #2

=INDEX(tbl, SMALL(OR(
IF((INDEX(tbl, , 1, 1)<="Plumbing")*(INDEX(tbl, , 1, 1)>="Plumbing"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)),
IF((INDEX(tbl, , 1, 1)<="Plumbing,Electrical")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)),
IF((INDEX(tbl, , 1, 1)<="Plumbing,Interior")*(INDEX(tbl, , 1, 1)>="Plumbing,Interior"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)),
IF((INDEX(tbl, , 1, 1)<="Plumbing,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Exterior"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)),
IF((INDEX(tbl, , 1, 1)<="Plumbing,Electrical,Interior")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical,Interior"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)),
IF((INDEX(tbl, , 1, 1)<="Plumbing,Electrical,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical,Exterior"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)),
IF((INDEX(tbl, , 1, 1)<="Plumbing,Interior,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Interior,Exterior"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)),
IF((INDEX(tbl, , 1, 1)<="Plumbing,Electrical,Interior,Exterior")*(INDEX(tbl, , 1, 1)>="Plumbing,Electrical,Interior,Exterior"), (ROW(tbl)-MIN(ROW(tbl))+1, ""), (ROWS(T3:\$T\$3), , 1)))))

The first one works, however instead of only carrying over the requested rows, it pulls over all of the rows.
The second attempt returns an error as though it was not a formula... which doesn't make sense either as all of the different pieces are opened, closed and filled in properly.

What I have is a large list of items, numbers, descriptions etc. I am trying to sparse out only those rows that meet the above criteria in a constantly updating list. I have placed a data-validation-list in column A.

Anyone have any ideas? If there is a better way to do this, please let me know. If this is not possible, that would be nice to know too.

If you need more information as well, let me know and I'll see what I can do.

Thank you,

TKB

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

##### MrExcel MVP
Is it possible to have a small sample along the results that you want to see?

#### TKB

##### MrExcel MVP
Hello,

Here is a link to a mock document:

https://www.dropbox.com/s/3gzu0tq5fhbkfjo/Rehab%20Schedule%20Test%20File.xlsx?dl=0

What I am looking to do is have the array not only show the "Hello" lines, but I also want the "Hello, Hi" lines to show up in the same array. Any ideas?

In the document, you can change column A1 to Hello to have it show up in the array.

Thanks,

TKB

Is it possible that you provide a relevant small sample directly here? Try one of the following methods: Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste.

#### TKB

##### Board Regular
Is it possible that you provide a relevant small sample directly here? Try one of the following methods: Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste.

Sheet1

 * A B C D E F G H I J K L M N O P Q R S 1 Column1 Column2 Column3 Column4 Column5 Column6 * Set 1 Hello * * * * * * * * * * 2 Hello 1 1 1 1 1 * * Hello * * * * * * * * * * 3 Hello, Hi 2 2 2 2 2 * * * * * Hello 1 1 1 1 1 #N/A #N/A 4 Hi 3 3 3 3 3 * Set 2 Hello Hi * * Hello 5 5 5 5 5 #N/A #N/A 5 Howdy 4 4 4 4 4 * * * * * #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! 6 Hello 5 5 5 5 5 * Set 3 Hi * * #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM!

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75.2px;"><col style="width:75.2px;"><col style="width:75.2px;"><col style="width:75.2px;"><col style="width:75.2px;"><col style="width:75.2px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

 Cell Formula L3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} M3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} N3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} O3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} P3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} Q3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} R3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} S3 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L3:\$L\$3)), , 1)} L4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} M4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} N4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} O4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} P4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} Q4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} R4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} S4 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L4)), , 1)} L5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} M5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} N5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} O5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} P5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} Q5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} R5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} S5 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L5)), , 1)} L6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)} M6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)} N6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)} O6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)} P6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)} Q6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)} R6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)} S6 {=INDEX(tbl, SMALL(IF((INDEX(tbl, , \$I\$3, 1)<=\$I\$2)*(INDEX(tbl, , \$I\$3, 1)>=\$I\$1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROWS(L\$3:\$L6)), , 1)}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

So, what I am trying to do is get not just the rows "Hello", but also get the row "Hello, Hi" in the table because it also contains the word "Hello".

Also, and this is not absolutely necessary, If instead of using a < and > formula, therefore requiring 2 different spaces, if it could just refer to a single input, which doesn't even have to be a reference. For example, instead of having to have the formula refer to I1 and I2, if it could just be I1, or even better, if I could just input "Hello" into the formula, that would be great. I tried just inputting the text earlier... but it didn't work properly after I did.

Thanks for the help,

TKB

Last edited:

##### MrExcel MVP
TKB,

It is better describe a problem in words instead of using/discussing (non-working) formulas. Also, all records are the same (excepting the row headers). Such is probably not representative of your data and risks causing confusion.

A:F houses the sample data.

Problem: Collect/list the records of which the first field contains the substring "Hello".

 Field-1 Field-2 Field-3 Field-4 Field-5 Field-6 Hello 1 1 1 1 1 Hello, Hi 2 2 2 2 2 Hi 3 3 3 3 3 Howdy 4 4 4 4 4 Hello 5 5 5 5 5

<TBODY>
</TBODY>

Let's H:N house the desired processing...

 Hello Idx Field-1 Field-2 Field-3 Field-4 Field-5 Field-6 1 Hello 1 1 1 1 1 2 Hello, Hi 2 2 2 2 2 5 Hello 5 5 5 5 5

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3128" width=88><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3214" width=90><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width=96><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3242" width=91><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3527" width=99><TBODY>
</TBODY>

H3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IFERROR(SMALL(IF(ISNUMBER(SEARCH(H\$1,\$A\$2:\$A\$6)),
ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1),ROWS(\$H\$3:H3)),"")
``````

I3, just enter, copy across as far as needed, and then copy down:
Rich (BB code):
``````=IF(\$H3="","",INDEX(A\$2:A\$6,\$H3))
``````

Last edited:

#### TKB

##### Board Regular

Thank you very much for your help. The formula did exactly what I was looking for. However, I did find one small addition to the following formula to make it work properly:

=IF(\$T6="","",INDEX(A\$2:O\$200,\$T6,0))

Other than that small addition, it worked perfectly, and the 0 only took a couple of seconds to notice.

Again, thank you for your help, this has made my life a lot easier.

TKB

##### MrExcel MVP

Thank you very much for your help. The formula did exactly what I was looking for. However, I did find one small addition to the following formula to make it work properly:

=IF(\$T6="","",INDEX(A\$2:O\$200,\$T6,0))

Other than that small addition, it worked perfectly, and the 0 only took a couple of seconds to notice.

Again, thank you for your help, this has made my life a lot easier.

TKB

Great. However, I don't understand that 0...I suggested in fact something different:

=IF(\$T6="","",INDEX(A\$2:A\$200,\$T6))

copied across. The range INDEX is fed with changes when copying: A\$2:A\$200 --> B\$2:B\$200 --> C\$2:C\$200, ... etc.

Last edited:

#### TKB

##### Board Regular
I dont understand it either to be honest. I assumed the same thing. Although I am obviously not as proficient as you in the formulas, I've dealt enough with it to understand how a lot of this works. Therefore, I assumed that it would do just as you stated, change the column reference as it is copied across. Maybe I have a weird setting I don't know about or something. Either way, it's working now so im not going to touch it just in case it decides to blow up on me.

Thanks again for the help,

TKB

Replies
6
Views
383
Replies
6
Views
358
Replies
3
Views
2K
Replies
3
Views
181
Replies
0
Views
153

1,191,204
Messages
5,985,262
Members
439,953
Latest member
suchitha

### 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