Table of contents

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

Answer:

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.

Download excel *.xlsx file

SBabu.xlsx

Lookup multiple values in different columns and return multiple values

Jason C asks:

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:

=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

Download excel *.xlsx file

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

Related posts:

Lookup and return multiple values from a range excluding blanks

Lookup values in a range using two or more criteria and return multiple matches in excel

Excel: Look for values containing a lookup value and return multiple values horizontally

Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel

Lookup a value in a list and return multiple matches in excel