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

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

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. Click on "Evaluate Formula" button to start evaluating.

A dialog box appears, click 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.

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!