You are here:

Excel/Extract a List of Values Filtered by Criteria with Sub-Arrays


Hi Tom

Hope l find you well.

May you please assist.

I am working on a project where I wanted to identify the fuel economy of a specific vehicle and then list all the vehicles that meet a minimum miles per gallon (MPG) rating in a table on tab "Filtered List". A2 contains the criteria.
For each vehicle that qualifies, I want to list out the basic information about the model and its mileage.

All car data is stored on the worksheet Car Data (Namibia), and Car Data (Zambia),  with the filtered table  " Filtered list" tab

The formula I have put extracts from Car Data (Namibia), I would like to extend it to also include Car Data (Zambia). Will it be possible to amend the formulae on tab "Filtered List" to search both tables. I have used array formula below:

{=IFERROR(INDEX('Car Data (Namibia)'!B$2:B$1156,SMALL(IF('Car Data (Namibia)'!$H$2:$H$1156>='Filtered List'!$A$2,ROW('Car Data (Namibia)'!B$2:B$1156)-ROW('Car Data (Namibia)'!B$2)+1),ROWS('Car Data (Namibia)'!B$2:'Car Data (Namibia)'!B2))),"")}

Thank you looking forward to a favorable reply.


basically you can do
=if(row()<=countif('Car Data (Namibia)'!$H$2:$H$1156,">="&'Filtered List'!$A$2),Namibia Formula, Zambia formula)

You would have to adjust the "row()" to return a number that can be compared to the countif.  Since I don't know where your formula will start, then I can't really provide the exact formula.

That is the basic approach.  A second problem is to adjust the second argument to SMALL in the Zambia formula so it discounts the number of rows returned from the Namibia sheet.

ROWS('Car Data (Namibia)'!B$2:'Car Data (Namibia)'!B2

for the Zambia formula rather than using

ROWS('Car Data (Zambia)'!B$2:'Car Data (Zambia)'!B2

You separately sent me your file - so here is the formula that worked for me (placed in A5 of the Filter List sheet.

=IF((ROW()-4)<=COUNTIF('Car Data (Namibia)'!$H$2:$H$1156,">="&'Filtered List'!$A$2),INDEX('Car Data (Namibia)'!B$2:B$1156,SMALL(IF('Car Data (Namibia)'!$H$2:$H$1156>='Filtered List'!$A$2,ROW('Car Data (Namibia)'!B$2:B$1156)-ROW('Car Data (Namibia)'!B$2)+1),ROWS('Car Data (Namibia)'!B$2:'Car Data (Namibia)'!B2))),IFERROR(INDEX('Car Data (Zambia)'!B$2:B$1156,SMALL(IF('Car Data (Zambia)'!$H$2:$H$1156>='Filtered List'!$A$2,ROW('Car Data (Zambia)'!B$2:B$1156)-ROW('Car Data (Zambia)'!B$2)+1),ROW()-4-COUNTIF('Car Data (Namibia)'!$H$2:$H$1156,">="&'Filtered List'!$A$2))),""))

Array formula entered with Ctrl+Shift+enter since this is an array formula

drag across and fill down

Worked for me.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.