How to use index match with IF in excel? -

table1

           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:

              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 need put in column 2 equation this:

index(table1[item],match(table2[seq],tabel1[seq],0) table1[reorder qty] > table1[on-hand qty] 

i'm not sure how such requirement managed?

this can done. requires use of array formula in table2.

normally index use range of cells array (first argument of formula). in case, give new array return based on results of conditional (your where clause).

i start picture of results , give formulas. me, table1 on left, table2 on right.

formulas

the formulas similar, main difference column return in if part generates array index. conditional part of if same columns. note using tables here helps copying around formulas since ranges cannot change under us.

these array formulas , need entered 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 new array based on conditional. new array return desired column (item, re-order, ...) or return n/a if conditional false. requires array formula entry since going row row in if.
• the match part of formula row number "standard". looking seq number in table1. determines row of new array return.