Array Formula Help (lookup)

mtharnden

Board Regular
Joined
Aug 17, 2011
Messages
114
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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).
 
Upvote 0
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 :p
=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
 
Upvote 0
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"

http://www.contextures.com/xladvfilter01_2003.html#ApplyAF
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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