Multiple Option Table Array

T

TKB

Board Regular
Joined
Aug 18, 2011
Messages
128
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
TKB said:
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
Click to expand...

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.
 
Upvote 0
Aladin Akyurek said:
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.
Click to expand...

Sheet1

*ABCDEFGHIJKLMNOPQRS
1Column1Column2Column3Column4Column5Column6*Set 1Hello**********
2Hello11111**Hello**********
3Hello, Hi22222*****Hello11111#N/A#N/A
4Hi33333*Set 2Hello Hi**Hello55555#N/A#N/A
5Howdy44444*****#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!#NUM!
6Hello55555*Set 3Hi**#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>

Spreadsheet Formulas
CellFormula
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:
Upvote 0
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
IdxField-1Field-2Field-3Field-4Field-5Field-6
1Hello11111
2Hello, Hi22222
5Hello55555

<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:
Upvote 0
Hello Aladin,

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
 
Upvote 0
TKB said:
Hello Aladin,

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
Click to expand...

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:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Compare similar company names between two tables and return one of the values
Replies
2
Views
335
realtoast
R
T
  • Question
VBA inserting formula's in cells not working
Replies
7
Views
316
Peter_SSs
Peter_SSs
P
  • Question
Copy data from one table to multiple table.
Replies
0
Views
352
patapsychonaute
P
B
  • Question
HELP! - Need Updated logic and I'm not sure how to do it
Replies
6
Views
198
dreid1011
dreid1011
D
  • Solved
Vlookup/Index that returns all distinct values, but returns in a row?
Replies
8
Views
376
Fluff
Fluff

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

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
Back
Top