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

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

Comments(31) Filed in category: Excel, Vlookup

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

Comments(2) Filed in category: Count values, Excel

**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}

Comments(9) Filed in category: Excel

**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.

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel

**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.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

### Download excel *.xlsx file

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

### Category: Vlookup and return multiple values

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Improve your VLOOKUP formula and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]Comments(157) Filed in category: Excel, VLOOKUP and return multiple values

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values on the same row from a range excluding blanks

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted […]Comments(14) Filed in category: Excel, VLOOKUP and return multiple values

Use a drop down list to search and return multiple values

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles […]Comments(12) Filed in category: Drop down lists, Excel, VLOOKUP and return multiple values

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]Comments(2) Filed in category: Excel, VLOOKUP and return multiple values

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

Unique distinct list sorted alphabetically and based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

Search case sensitive and return multiple values

Array formula in D4: =INDEX($A$1:$A$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell D4 and […]Comments(0) Filed in category: Case sensitive, Excel, VLOOKUP and return multiple values