Function Problems

How to Create a List Based on Another Column in Excel Without FILTER

Elise Williams Oct 14, 2026 870 views

Question details

The user needs to dynamically extract and distribute values from a master table into specific category columns based on multiple criteria, including linked checkboxes.

How to Create a List Based on Another Column in Excel Without FILTER

Product: Excel
Device & OS: not provided
Scenario: Filtering values from Table3 column A into corresponding hospital columns (N through T) when a linked checkbox is selected and the hospital name matches, specifically in versions without the modern FILTER function.
Observed behavior: A dynamic list that updates and displays matching records under the correct column headers without leaving blank rows in between.

Before you start

Ensure that your form control checkboxes are properly linked to cells (e.g., Column C) so they return a TRUE or FALSE value, and verify that your target column headers exactly match the criteria you are filtering by.

Solution 1Recommended

Use an INDEX and SMALL Array Formula

This method combines several functions to evaluate multiple conditions and extract matching records, serving as an alternative for users without the dynamic FILTER function.

By utilizing an array formula with INDEX, SMALL, IF, and ROW, Excel evaluates the entire data range simultaneously. The IF function checks for both the TRUE value of the checkbox and the matching hospital name. The SMALL function extracts the row numbers one by one, and INDEX retrieves the final corresponding value from column A.

Verify Linked Checkbox Cells

Confirm that column C contains TRUE for rows where the checkbox is selected. If not, right-click the checkbox, select 'Format Control', and set the 'Cell link' to the corresponding cell in column C.

Enter the Array Formula

Select the first target cell in your category column (e.g., N2). Type the following formula: =IFERROR(INDEX($A$2:$A$100,SMALL(IF(($C$2:$C$100=TRUE)*($L$2:$L$100=N$1),MATCH(ROW($A$2:$A$100),ROW($A$2:$A$100)),""),ROWS(N$2:N2))),"")

Confirm as an Array Formula

Depending on your spreadsheet version, you may need to press Ctrl + Shift + Enter to execute the formula correctly. You will see curly brackets {} appear around the formula if done successfully.

Fill the Formula Down and Right

Click and drag the fill handle at the bottom-right of cell N2 downwards to extract all possible matches, then drag it across through column T to apply it to all hospital categories.

Absolute References are Crucial: Notice the use of dollar signs ($) in the formula. These absolute references ensure your data ranges ($A$2:$A$100) do not shift when you copy the formula to other cells, while N$1 allows the column to change but keeps the header row locked.

Advanced Spreadsheet Functions

Filter and Extract Data Easily with WPS Spreadsheet

WPS Spreadsheet seamlessly supports advanced array formulas as well as the modern dynamic FILTER function, making complex data extraction and list management effortless.

1. Open Your Dataset: Launch WPS Spreadsheet and open the document containing your source table and target columns.
2. Apply the FILTER Function: Instead of a complex array formula, simply select your target cell (N2) and type: =FILTER(A2:A100, (C2:C100=TRUE)*(L2:L100=N1), "")
3. Press Enter to Populate: Press Enter, and WPS Spreadsheet will instantly spill all matching records down the column automatically.

Fully compatible with Microsoft Excel formulas and file formatsSupports advanced array functions including INDEX, SMALL, and MATCHIncludes the modern FILTER function for significantly easier data extractionLightweight, fast, and completely free for daily spreadsheet processing

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDEX and SMALL formula returning blank cells?

This usually happens if the linked checkbox cells do not actually contain the boolean value TRUE, if the data ranges in the formula are not locked with absolute references ($), or if you did not press Ctrl+Shift+Enter to confirm it as an array formula.

Is there an easier way to create a list based on criteria without complex arrays?

Yes. If you are using a newer spreadsheet version that supports dynamic arrays, you can use the FILTER function. It replaces the entire INDEX/SMALL combination with a simple formula like =FILTER(Return_Range, Criteria_Range=Condition).

How do I correctly link a form checkbox to a cell?

Right-click the checkbox and select 'Format Control'. Go to the 'Control' tab, click inside the 'Cell link' box, and then select the cell you want to link it to (e.g., C2). The cell will now display TRUE when checked and FALSE when unchecked.

Excel & Data Problems