### Lookup multiple values in different columns and return a single value

Dear Oscar,
I m working on the below table.
ORDER MODEL MATERIAL QTY STATUS
BOM a s6 1 COMPLETED
BOM b c6 2 NOT COMPLETED
BOM c s6 1 COMPLETED
DEL d c6 3 NOT COMPLETED
EXP a a8 4 IN PROGRESS
DEL b d2 5 COMPLETED
DEL c c6 4 NOT COMPLETED
DEL d s6 7 NOT COMPLETED
DEL e c6 8 NOT COMPLETED
DEL r a8 1 COMPLETED
EXP g d1 5 COMPLETED
EXP r c6 9 COMPLETED
EXP t a8 2 COMPLETED
EXP a c6 1 NOT COMPLETED
EXP b s6 9 COMPLETED
EXP c c6 1 NOT COMPLETED
EXP d a8 4 NOT COMPLETED
I need the status column to be vlooked up on another file by comparing all the remaining 4 columns.(the sheet to be updated carries the 4 columns not in the same order as in the original sheet.. its mixed).. Pls help me with dis..
Thanks
S.Babu

Sheet1

Sheet2

Array formula in cell E2, sheet2:

=INDEX(Sheet1!\$E\$2:\$E\$18, MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!\$D\$2:\$D\$18, Sheet2!B2, Sheet1!\$B\$2:\$B\$18, Sheet2!C2, Sheet1!\$C\$2:\$C\$18, Sheet2!D2, Sheet1!\$A\$2:\$A\$18), 0))

How to create an array formula

1. Select cell E2
2. Paste array formula
3. Press and hold Ctrl + Shift
4. Press Enter

How to copy array formula

1. Select cell E2
2. Copy cell (Ctrl + c)
3. Select cell E3:E20
4. Paste (Ctrl + v)

Explaining array formula in cell E2

Step 1 - Count the number of cells by a given set of criteria

COUNTIFS(Sheet2!A2, Sheet1!\$D\$2:\$D\$18, Sheet2!B2, Sheet1!\$B\$2:\$B\$18, Sheet2!C2, Sheet1!\$C\$2:\$C\$18, Sheet2!D2, Sheet1!\$A\$2:\$A\$18)

becomes

COUNTIFS(Sheet2!A2, Sheet1!\$D\$2:\$D\$18, Sheet2!B2, Sheet1!\$B\$2:\$B\$18, Sheet2!C2, Sheet1!\$C\$2:\$C\$18, Sheet2!D2, Sheet1!\$A\$2:\$A\$18)

and returns {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

Step 2 - Return the relative position of an item in an array that matches a specified value

MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!\$D\$2:\$D\$18, Sheet2!B2, Sheet1!\$B\$2:\$B\$18, Sheet2!C2, Sheet1!\$C\$2:\$C\$18, Sheet2!D2, Sheet1!\$A\$2:\$A\$18), 0)

becomes

MATCH(1, {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, 0)

and returns 3.

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

INDEX(Sheet1!\$E\$2:\$E\$18, MATCH(1, COUNTIFS(Sheet2!A2, Sheet1!\$D\$2:\$D\$18, Sheet2!B2, Sheet1!\$B\$2:\$B\$18, Sheet2!C2, Sheet1!\$C\$2:\$C\$18, Sheet2!D2, Sheet1!\$A\$2:\$A\$18), 0))

becomes

INDEX(Sheet1!\$E\$2:\$E\$18, 3)

and returns "COMPLETED" in cell E2.

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

Array formula in cell B18:

=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)))

How to create an array formula

See steps above!

How to copy an array fomula

See steps above!

### Explaining array formula in cell B18

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

(\$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

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}

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

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