# Array Formula Help (lookup)

#### mtharnden

##### Board Regular
hello, i am trying to get excel to display all values in Column B where Column F = 1

i know that an Array Formula will do this, i just have only used those one time and am not finding what i need on here.

i can use an index/match to get the first occurance, but when i make it an array and drag down it doesnt continue on to the next one...

Code:
``=INDEX(B:B,MATCH(1,F:F,0))``

anyone here who can help me that would be appreciated

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### XOR LX

##### Well-known Member
Hi,

Assuming your data goes from row 1 to row 1000 (adjust the below ranges to suit your actual needs), put this formula in cell J1:

=COUNTIF(F1:F11000,1)

Then enter this array formula** in your first cell of choice:

=IF(ROWS(\$1:1)>\$J\$1,"",INDEX(\$B\$1:\$B\$1000,SMALL(IF(\$F\$1:\$F\$1000=1,ROW(\$F\$1:\$F\$1000)-MIN(ROW(\$F\$1:\$F\$1000))+1),ROWS(\$1:1))))

Copy this formula down (though not the one in J1 - that's a one-off calculation) until you start to get blanks for the results.

Note that this solution depends on your values in column F being the numerical 1, and not the text entry 1 which just happens to look like the numerical version. If you're not sure, pick one of the cells containing one of these 1s, e.g. F3, and enter this formula in another cell somewhere:

=ISNUMBER(F3)

A TRUE will confirm that it is indeed a number, and so you have nothing to worry about!

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

#### Gilliam

##### Active Member
I think the easiest way is to list them in ascending order using LARGE,
if this won't work for you post again and we'll dive into the bigger equations
=IFERROR(LARGE((\$F\$1:\$F\$10000=1)*\$B\$1:\$B\$10000,COUNT(B1:\$B\$10000)-COUNTIF(\$F\$1:\$F\$10000,"<>1")),"") Entered with Ctrl + Shift + Enter

#### mtharnden

##### Board Regular
Hi,

Assuming your data goes from row 1 to row 1000 (adjust the below ranges to suit your actual needs), put this formula in cell J1:

=COUNTIF(F1:F11000,1)

Then enter this array formula** in your first cell of choice:

=IF(ROWS(\$1:1)>\$J\$1,"",INDEX(\$B\$1:\$B\$1000,SMALL(IF(\$F\$1:\$F\$1000=1,ROW(\$F\$1:\$F\$1000)-MIN(ROW(\$F\$1:\$F\$1000))+1),ROWS(\$1:1))))

Copy this formula down (though not the one in J1 - that's a one-off calculation) until you start to get blanks for the results.

Note that this solution depends on your values in column F being the numerical 1, and not the text entry 1 which just happens to look like the numerical version. If you're not sure, pick one of the cells containing one of these 1s, e.g. F3, and enter this formula in another cell somewhere:

=ISNUMBER(F3)

A TRUE will confirm that it is indeed a number, and so you have nothing to worry about!

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

THank you both, this one worked. i went agead and put that J1 part into the formula.

also for people who read this looking for answers i learned you can do an advanced filter to a different locaiton that will give the same results. the only downside to that is you'd have to utilize a macro to have an "auto refresh"

#### XOR LX

##### Well-known Member
THank you both, this one worked. i went agead and put that J1 part into the formula.

You're welcome!

Replies
5
Views
435
Replies
3
Views
281
Replies
3
Views
124
Replies
10
Views
325
Replies
6
Views
207

1,191,187
Messages
5,985,194
Members
439,947
Latest member
fabiannic

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