Author: Oscar Cronquist Article last updated on August 21, 2019

This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns.

S.Babu asks:

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

https://youtu.be/LX6UUTCMo_Y

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

Lookup multiple values in different columns and return multiple values

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

Use VLOOKUP and return multiple values sorted from A to Z

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

How to enter an array formula

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

How to enter an array formula

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}

How to use the COUNTIFS function

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

How to use the COUNTIFS function

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 the MATCH function

Identify the position of a value in an array.

How to use the MATCH function

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.

How to use the INDEX function

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

How to use the INDEX function

Download excel *.xlsx file

SBabu.xlsx