Author: Oscar Cronquist Article last updated on August 26, 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

Sheet1 - Data

Sheet2 - Criteria and result

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:

Recommended articles

test
Lookup based on a date range and a condition return multiple values
This article demonstrates a formula that extracts values from a column based on a date range criteria and another condition. […]

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

Recommended articles

test
Use VLOOKUP and return multiple values sorted from A to Z
This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]

How to create an array formula

  1. Copy above array formula
  2. Double press with left mouse button on cell E2
  3. Paste array formula
  4. Press and hold Ctrl + Shift simultaneously
  5. Press Enter once
  6. Release all keys

Recommended articles

test
A beginners guide to Excel array formulas
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

I recommend that you use the "Evaluate Formula" feature in Excel to troubleshoot or to simply understand how a formula works.

Select the cell containing the formula you want to evaluate, go to tab "Formulas" on the ribbon. Press with mouse on "Evaluate Formula" button to start evaluating.

A dialog box appears, press with left mouse button on the "Evaluate" button to go through the formula calculations step by step.

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

The COUNTIFS function can work with up to 127 argument pairs:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

There are four criteria and the COUNTIFS function requires eight arguments, in order to get an array of values that we can use the second argument in each pair is a cell range.

You are probably not used to this setup but it works fine, the array allows you to identify where the match is or in other words where all criteria match.

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)

returns the following array {0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}.

This array tells us that the match is in row 3 because 1 is in the third position in the array. Note that all criteria must match in order to return 1.

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

The MATCH function returns the relative position of a value in a cell range or array.

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. Value 1 is found in the third position in the array.

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

The INDEX function returns a value based on a row and column number, there is only a row number in this case so you can omit the column argument.

INDEX(cell_reference, [row_num], [column_num])

The first argument is the cell reference from which you want to get a specific value from.

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.

Get the Excel file


SBabu.xlsx