Get value using INDEX and MATCH

0
votes

I need help in my formula, I'm getting #VALUE!. I have 2 sheets, I need to get the data using 2 values, So I've searched and the solution is to use the INDEX and MATCH Function. I've tried that in my formula but I think I'm doing it wrong.

I have 3 columns in my Main Sheet,

Main Sheet

In the column Total Amount I need to insert the formula.

And this is my 2nd sheet. 3 columns also.

FUND NAME DATE TOTAL AMOUNT FUND 1/10/2018 19,247,423.00 FUND 2/10/2018 64,223,123.00 FUND 3/10/2018 21,788,343.00 FUND 4/10/2018 55,534,489.00 FUND 5/10/2018 1,122,873.00

And this is the formula that I've used, =INDEX(Q9:Q13,MATCH(O19&P19,O9:O13&P9:P13,0))

I've tried to check using evaluate formula and I'm getting #Value! I don't know why.

Any Ideas? Thanks!

1
excelexcel-formula
You need to mention sheet name for index array like =INDEX(Sheet2!Q9:Q13,MATCH(O19&P19,Sheet2!O9:O13&Sheet2!P9:P13,0)). Then you need to enter the formula as an array formula as you are matching two column. Need to enter as CTRL + SHIFT + ENTER. You can use SUMIFS() to get your output. See my answer. - Harun24HR

1 Answers

1
votes

I think SUMIF() would be best fit for you in this case. Try below formula.

=SUMIFS(Sheet2!$Q$8:$Q$13,Sheet2!$O$8:$O$13,A1,Sheet2!$P$8:$P$13,B2)

enter image description here

You can also use SUMPRODUCT() like below.

=SUMPRODUCT((Sheet2!$Q$8:$Q$12)*(Sheet2!$O$8:$O$12=A2)*(Sheet2!$P$8:$P$12=B2))