## Filter values based on numerical ranges

*Article last updated on November 02, 2017*

Thank you *so* much for your detailed examples and actively replying to users! I have a problem, which I've tried solving by editing your formula examples for 20+ hours without a success although I thought I could do it myself but apparently not, so here goes:

I have a long list of organizations that work in specific zip areas defined by zip ranges (start and end). One org might have multiple zip ranges and there can be overlap between organizations (i.e. one zip might "belong" to >1 org). Then there's another list that has got all the possible existing zips. I would need to have all existing zips falling inside the zip range of the organization added to separate columns on the matching row of the first list.

First list:

org name | zip range start | zip range end

org 1 | 00100 | 00200

org 2 | 00180 | 00250

org 1 | 00220 | 00230

Second list:

00100

00110

00190

00220

00225

Desired result:

org name | zip1 | zip2 | zip3 | zip n...

org 1 | 00100 | 00110 | 00190

org 2 | 00190 | 00220 | 00225

org 1 | 00220 | 00225

Perfect result:

org name | zip1 | zip2 | zip3 | zip n...

org 1 | 00100 | 00110 | 00190 | 00220 | 00225

org 2 | 00190 | 00220 | 00225

This would be of HUGE help if you could solve the problem. Thank you very much already for all the help, your examples have provided me with tons of new Excel wizardry skills.

Best wishes,

Eero

Thank you for a great question. The first list is in cell range B2:F5, second list is in B7:B11.

**Formula in cell B15:**

Copy cell B15 and paste to cells below as far as needed. This formula extracts unique distinct values, you can read about the formula in more detail here:

How to extract a unique distinct list

**Array formula in cell C15:**

Copy cell C15 and paste to adjacent cells to the right and then to cells below. This formula has the ability to use more than one numerical range simultaneously as criteria.

Example, org 1 has two ranges 100-200 and 220-230. org 2 has only one numerical range, 180 -250.

### Explaining formula in cell C15

**Step 1 - Filter start range values**

The IF function allows you to filter values, in this case a condition applied to cell range B3:B5 and return corresponding values in cell range C3:C5

IF($B15=$B$3:$B$5, $C$3:$C$5, "")

becomes

IF("org 1 "={"org 1 ";"org 2 ";"org 1 "},{100;180;220},"")

becomes

IF({"TRUE";"FALSE";"TRUE"},{100;180;220},"")

and returns the following array: {100; ""; 220}

**Step 2 - Compare with list**

IF($B15=$B$3:$B$5, $C$3:$C$5, "")<=TRANSPOSE($B$8:$B$12)

becomes

{100; ""; 220}<=TRANSPOSE($B$8:$B$12)

becomes

{100; ""; 220}<={100,110,190,220,225}

and returns {TRUE, TRUE, TRUE, TRUE, TRUE; FALSE, FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, TRUE, TRUE}

**Step 3 - Filter end range values**

IF($B15=$B$3:$B$5, $E$3:$E$5, "")

becomes

IF("org 1 "={"org 1 ";"org 2 ";"org 1 "},{200;"";230},"")

becomes

IF({"TRUE";"FALSE";"TRUE"},{200;"";230},"")

and returns the following array: {200;"";230}

**Step 4 - Compare with list**

IF($B15=$B$3:$B$5, $E$3:$E$5, "")>=TRANSPOSE($B$8:$B$12)

becomes

{200;"";230}>=TRANSPOSE($B$8:$B$12)

becomes

{200;"";230}>={100,110,190,220,225}

and returns {FALSE, FALSE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE}

**Step 5 - Filter values**

Multiplying both logical expressions gives this formula:

IF((IF($B15=$B$3:$B$5, $C$3:$C$5, "")<=TRANSPOSE($B$8:$B$12))*(IF($B15=$B$3:$B$5, $E$3:$E$5, "")>=TRANSPOSE($B$8:$B$12)), TRANSPOSE($B$8:$B$12), "")

becomes

IF({TRUE, TRUE, TRUE, TRUE, TRUE; FALSE, FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, TRUE, TRUE}*{FALSE, FALSE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE}, TRANSPOSE($B$8:$B$12), "")

becomes

IF({1,1,1,0,0;0,0,0,0,0;0,0,0,1,1}, TRANSPOSE($B$8:$B$12), "")

becomes

IF({1,1,1,0,0;0,0,0,0,0;0,0,0,1,1}, {100,110,190,220,225}, "")

and returns this array:

{100,110,190,"","";"","","","","";"","","",220,225}

The following picture shows an index table that has values horizontally and what range they are in.

Example, values in range 100-200 are 100, 110 and 190 because the array has number 1 (TRUE) in those locations.

**Step 6 - Find the k-th s****mallest value**

SMALL(IF((IF($B15=$B$3:$B$5, $C$3:$C$5, "")<=TRANSPOSE($B$8:$B$12))*(IF($B15=$B$3:$B$5, $E$3:$E$5, "")>=TRANSPOSE($B$8:$B$12)), TRANSPOSE($B$8:$B$12), ""), COLUMNS($A$1:A1))

becomes

SMALL({100,110,190,"","";"","","","","";"","","",220,225}, COLUMNS($A$1:A1))

becomes

SMALL({100,110,190,"","";"","","","","";"","","",220,225}, 1)

and returns 100 in cell C15.

### Download excel *.xlsx file

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form