## Lookup multiple values in different columns and return multiple values

*Article updated on August 25, 2017*

I have a set of data, like the one you used in the original example that also has a column for the date of the transaction. I would like my Index-type formula to search for both the main item (the rep's name) and also if the date of the transaction falls in the date range).

Start Date: 11/26/2012

End Date: 11/30/2012 (both entered by the user)

Rep: John

Then the results, in each row/column of the 'result' section (INDEX formula results) would show results for John that occurred from 11/26 to 11/30 (including both dates).

Thanks for any help with the formula for that.

Jason

**Answer:**

**Array formula in cell B18:**

Lookup multiple values in different columns and return a single value

S.Babu asks: Dear Oscar, I m working on the below table. ORDER MODEL MATERIAL QTY STATUS BOM a s6 1 […]

**How to create an array formula**

- Copy above array formula
- Double click on cell E2
- Paste array formula
- Press and hold Ctrl + Shift simultaneously
- Press Enter once
- Release all keys

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

**How to copy array formula**

- Select cell E2
- Copy cell (Ctrl + c)
- Select cell E3:E20
- Paste (Ctrl + v)

### Explaining array formula in cell B18

You can easily follow along, select cell B18. Go to tab "Formulas" and click "Evaluate formula" button.

Click "Evaluate" button, shown in above picture, to move to next step.

**Step 1 - Compare value in cell B15 to cell range $B$2:$B$11**

**Step 1 - Compare value in cell B15 to cell range $B$2:$B$11**

$B$15=$B$2:$B$11

becomes

"John"={"John";"Jennifer";"Laura";"Paul";"John";"Laura";"Jennifer";"Paul";"Paul";"John"}

and returns

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

**Step 2 - Compare start and end date to date column**

**Step 2 - Compare start and end date to date column**

($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13)

becomes

({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE})*({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE})

and returns

{1;1;1;1;1;1;1;0;0;0}

**Step 3 - If a record is a match return it´s row number**

**Step 3 - If a record is a match return it´s row number**

IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11))

becomes

IF({TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}*{1;1;1;1;1;1;1;0;0;0},MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11))

becomes

IF({1;0;0;0;1;0;0;0;0;0},{1; 2; 3; 4; 5; 6; 7; 8; 9; 10})

and returns

{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE}

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

**Step 4 - Return the k-th smallest value**

**Step 4 - Return the k-th smallest value**

SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),""),ROW(A1))

becomes

SMALL({1; FALSE; FALSE; FALSE; 5; FALSE; FALSE; FALSE; FALSE; FALSE},ROW(A1))

becomes

SMALL({1; FALSE; FALSE; FALSE; 5; FALSE; FALSE; FALSE; FALSE; FALSE},1)

and returns 1.

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

**Step 5 - Return the value of a cell at the intersection of a particular row and column**

**Step 5 - Return the value of a cell at the intersection of a particular row and column**

INDEX($C$2:$C$11,SMALL(IF(($B$15=$B$2:$B$11)*($A$2:$A$11<=$B$14)*($A$2:$A$11>=$B$13),MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)),""),ROW(A1)))

becomes

INDEX($C$2:$C$11,1)

becomes

INDEX({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"},1)

and returns A in cell B18.

Gets a value in a specific cell range based on a row and column number.

### Download excel *.xlsx file

Lookup multiple values in different columns and return multiple valuesv2.xlsx

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]### 2 Responses to “Lookup multiple values in different columns and return multiple values”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I just wonder if the following two solutions might be preferable to your complex array entered formula:

Since you presented the data in an Excel table

1 by filtering the Date and Rep columns, you will find your answer within seconds, with no programming.

2 by using Get & Transform (Power Query), we can filter the entire table to return the results Jason wants:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= #date(2012, 1, 1) and [Date] <= #date(2012, 1, 6) and [Rep] = "John")

In both cases, changes to the start and end dates and to the Rep can be made very easily.

Duncan Williamson,

I just wonder if the following two solutions might be preferable to your complex array entered formula:Yes, I know. You can also use an Excel defined Table.

This is a formula solution, this is sometimes useful in a dashboard or perhaps a dynamic chart.

Thank you for commenting.