Index and match Multiple Criteria

[Nuttawut Udom]

How to use formula Below in Vba. I have tried many methods but still can't find the right answer.


"=INDEX('[Master Plan 91 20072024.xlsm]Master Plan'!$I:$I,MATCH(E3&P3,'[Master Plan 91 20072024.xlsm]Master Plan'!$H:$H&'[Master Plan 91 20072024.xlsm]Master Plan'!$AL:$AL,0))"


Thanks for all the answers

 

[Eric W]

Welcome to the MrExcel forum.

Have you tried:

Range("A3").FormulaArray ="=INDEX('[Master Plan 91 20072024.xlsm]Master Plan'!$I:$I,MATCH(E3&P3,'[Master Plan 91 20072024.xlsm]Master Plan'!$H:$H&'[Master Plan 91 20072024.xlsm]Master Plan'!$AL:$AL,0))"

Also note that the formula will end up relative to the target range.

 

[Nuttawut Udom]

Let me try it first.

 

[Nuttawut Udom]

How do I write it in the application.worksheetfunction format?

 

[Nuttawut Udom]

This FormulaArray Stick to one line only, don't follow other lines. Range("A3").FormulaArray ="=INDEX('[Master Plan 91 20072024.xlsm]Master Plan'!$I:$I,MATCH(E3&P3,'[Master Plan 91 20072024.xlsm]Master Plan'!$H:$H&'[Master Plan 91 20072024.xlsm]Master Plan'!$AL:$AL,0))"

 

[Eric W]

I'm not sure what your question is. What exactly are you trying to achieve?

It's possible to assign the formula to a range if that's what you want. Like this:

Range("A3:A10").FormulaArray ="=INDEX('[Master Plan 91 20072024.xlsm]Master Plan'!$I:$I,MATCH(E3&P3,'[Master Plan 91 20072024.xlsm]Master Plan'!$H:$H&'[Master Plan 91 20072024.xlsm]Master Plan'!$AL:$AL,0))"

As I said before the ranges will be relative, so that in the A3 cell E3&P3 will be E3&P3, in the A4 cell E3&P3 will become E4&P4, and so on. If you want something more complicated, you could put the statement in a loop, and manually create the formula string each time through, making the individual changes as needed.

 

[Nuttawut Udom]

I’ll try thanks