using match function with multiple criteria

[monoj chakraborty]

I am trying to pull a column from one sheet to another using the index and match function. But the match function needs to match data across two columns (I know that concatenate and vlookup might solve the problem).

So, I need to pull the data from col. "gm" from sheet3 into sheet2, by matching conditions in cols A & B.

Not sure why my formula is returning error as you can see in "sheet2".

Any urgent help highly appreciated.

 

[Nebu]

Hi:

Formula in yellow column.

Note:
Not an array formula.

Thanks

 

[monoj chakraborty]

thanks for this Nebu - so you are saying that this cannot work as an array formula as I had written? when I am trying to evaluate the formula, the error seems to be happening when the array checking for condition, returns true and false in the correct way, but when multiplying with the search array, returns error. This is where I am getting confused.

 

[Nebu]

Hi:

You can use array formulas as well, there are different ways to skin a cat. However, the array formula you had used was wrong, search the net for index array formula to get the right syntax.

Thanks

 

[bosco_yip]

Or…………………

In "sheet2" E2, copied down :

=SUMIFS(Sheet3!D:D,Sheet3!A:A,A2,Sheet3!B:B,B2)

Regards
Bosco

 

[monoj chakraborty]

Hi:

You can use array formulas as well, there are different ways to skin a cat. However, the array formula you had used was wrong, search the net for index array formula to get the right syntax.

Thanks

Got it and thanks Nebu. Quick one : why is the "1" within the mmult function? what is it meant to do

 

[Nebu]

Hi:

1 means true.

Thanks