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

The following array formula uses the corresponding values in column A, B, C and D to do a lookup in Sheet1 and return a value in column E.

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

Watch a video explaining formula above

Recommended article:

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 […]

The following article demonstrates how to do a lookup and return a sorted list:

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 […]

How to create an array formula

  1. Copy above array formula
  2. Double click on cell E2
  3. Paste array formula
  4. Press and hold Ctrl + Shift simultaneously
  5. Press Enter once
  6. 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

  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}

COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

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.

How to use Excel’s MATCH function

Identify the position of a value in an array.

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.

INDEX function explained

Fetch a value in a data set based on coordinates.

Download excel *.xlsx file

SBabu.xlsx