CODEWITHSUNDEEP
excel
Mahmoud Tarief
Mahmoud Tarief

Reputation: 75

How to use index match with IF in excel?

Table1

   A         B            C            D   
1 Seq        Item     Re-Order Qty  On-hand Qty
2  1          X         10           15
3  2          Y         10           5
4  3          Z         10           10

Other worksheet: Table2

Expected output:

    A           B            C
1   Seq       Item      Re-Order Qty
2   1          N/A          N/A
3   2           Y           10
4   3          N/A          N/A

In table2 I need to put in column 2 equation like this:

Index(Table1[Item],Match(table2[Seq],tabel1[Seq],0) WHERE table1[reorder qty] > table1[On-hand Qty]

I'm not sure how such requirement could be managed?

Upvotes: 1

Views: 27374

Answers (1)

Byron Wall
Byron Wall

Reputation: 4010

This can be done. It requires the use of an array formula in Table2.

Normally with an INDEX you simply use a range of cells as the array (first argument of the formula). In this case, we will give it a new array to return based on the results of a conditional (your WHERE clause).

I will start with the picture of results and then give the formulas. For me, Table1 is on the left, Table2 on the right.

picture of tables

Formulas

The formulas are very similar, the main difference is which column to return in the IF part which generates the array for INDEX. The conditional part of the IF is the same for all columns. Note that using Tables here really helps copying around the formulas since the ranges cannot change under us.

These are all array formulas and need to be entered with CTRL+SHIFT+ENTER.

Table2[Item]

=INDEX(IF(Table1[Re-Order Qty]>Table1[On-hand Qty],Table1[Item],"N/A"), MATCH([@Seq],Table1[Seq],0))

Table2[Re-Order Qty]

=INDEX(IF(Table1[Re-Order Qty]>Table1[On-hand Qty],Table1[Re-Order Qty],"N/A"), MATCH([@Seq],Table1[Seq],0))

Table2[On-hand Qty]

=INDEX(IF(Table1[Re-Order Qty]>Table1[On-hand Qty],Table1[On-hand Qty],"N/A"), MATCH([@Seq],Table1[Seq],0))

The main idea behind these formulas is:

  • Return a new array based on the conditional. This new array will return the desired column (Item, Re-order, ...) or it will return N/A if the conditional is FALSE. This requires the array formula entry since it is going row by row in the IF.
  • The MATCH part of the formula to get the row number is "standard". We are simply looking for the Seq number in Table1. This determines which row of the new array to return.

Upvotes: 1

Related Questions