## Return multiple values if in range

The image above shows a formula in cell C11 that extracts values from column D if the number in cell D9 is in a range specified in B3:B7 and C3:C7. This formula extracts multiple values if multiple ranges match.

The example above extracts B, C and D because 1.71 is between 1.45 - 1.72 (B), 1.67-1.99 (C) and 1.69-1.995 (D).

Array formula in cell C11:

### Explaining formula in cell C11

#### Step 1 - Check if number is larger than or equal to numbers in column B

The less than sign and the equal sign compares the numbers, if a number in column B is smaller than or equal to number in cell D9 then it evaluates to TRUE, if not FALSE.

$B$3:$B$7<=$D$9

becomes

{1.33;1.45;1.67;1.69;2}<=1.71

and returns

{TRUE; TRUE; TRUE; TRUE; FALSE}.

#### Step 2 - Check if number is smaller than or equal to numbers in column C

$C$3:$C$7>=$D$9

becomes

{1.66;1.72;1.99;1.995;2.33}>=1.71

and returns

{FALSE; TRUE; TRUE; TRUE; TRUE}

#### Step 3 - AND logic

Now we multiply the arrays, both logical expression must evaluate to TRUE in order to get the correct value(s). The parentheses are there to make sure that the correct calculation order is maintained.

($B$3:$B$7<=$D$9)*($C$3:$C$7>=$D$9)

becomes

{TRUE; TRUE; TRUE; TRUE; FALSE}*{FALSE; TRUE; TRUE; TRUE; TRUE}

and returns {0;1;1;1;0}.

#### Step 4 - Replace TRUE with row number

The IF function returns a unique number if boolean value is TRUE. FALSE returns "" (nothing).

IF(($B$3:$B$7<=$D$9)*($C$3:$C$7>=$D$9), MATCH(ROW($D$3:$D$7), ROW($D$3:$D$7)), "")

becomes

IF({0;1;1;1;0}, MATCH(ROW($D$3:$D$7), ROW($D$3:$D$7)), "")

becomes

IF({0;1;1;1;0}, {1;2;3;4;5}, "")

and returns {"";2;3;4;""}.

#### Step 5 - Extract k-th smallest row number

The SMALL function lets you calculate the k-th smallest value from a cell range or array. SMALL( *array*, *k*)

SMALL(IF(($B$3:$B$7<=$D$9)*($C$3:$C$7>=$D$9), MATCH(ROW($D$3:$D$7), ROW($D$3:$D$7)), ""), ROWS($A$1:A1))

becomes

SMALL({"";2;3;4;""}, ROWS($A$1:A1))

The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL({"";2;3;4;""}, ROWS($A$1:A1))

becomes

SMALL({"";2;3;4;""}, 1)

and returns 2.

#### Step 6 - Get corresponding value

The INDEX function returns a value based on row number (and column number if needed)

INDEX($D$3:$D$7, SMALL(IF(($B$3:$B$7<=$D$9)*($C$3:$C$7>=$D$9), MATCH(ROW($D$3:$D$7), ROW($D$3:$D$7)), ""), ROWS($A$1:A1)))

becomes

INDEX($D$3:$D$7, 2)

and returns "B" in cell C11.

### Get Excel *.xlsx file

### 2 Responses to “Return multiple values if in range”

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

i have query regarding returning multiple value against multiple checking and where i found value yes i have to return header row value by concatenate . example value

Mismatch - Recipient GSTIN Mismatch - GSTIN of the Supplier Mismatch - Invoice/Debit Note/ Credit Note (No) Mismatch - Invoice/Debit Note/ Credit Note (Date) Mismatch - Original Invoice No Mismatch - Original Invoice Date Mismatch - POS Mismatch - Supply attract reverse charge Mismatch - Total GST Rate Mismatch - Taxable Value Mismatch - IGST (Amt) Mismatch - CGST (Amt) Mismatch - SGST/UTGST (Amt) Mismatch - Cess(Amount)

No No No No No Yes Yes No No No Yes Yes Yes No

No No No yes No Yes Yes No No No Yes Yes Yes No

Hello Oscar,

I hope you are healthy and well in these troubled times.

re: https://www.get-digital-help.com/return-multiple-values-if-in-range-in-excel/

This doesn't work in Excel 2013 (or whichever one uses "|" instead of "," to separate arguments). Can you give me an update on what an updated version of your formula would be? Thank you very much!