Dynamic Array, Index & Match, SUMIFS Formula Help!

Hi Excel Forum Team,

I have a cash flow spreadsheet (see attachment or link to example below) that I need a formula for (A2:D6) that will automatically search through the larger cash flow spreadsheet below (A9:O13) and will return a single cost value that resided in a specified cell (D10:O13).

The formula will be able to find the correct horizontal column title (A9:O9), then once the formula finds the correct column title, it will search the vertical rows for three unique values WBS Code, Vendor Code, and Purchase Order Number (A10:C13). This will now allow the formula to target in on both horizontally and vertically to one cell that contains the specified project cost value (D10:O13) and return its findings back up to the specified month column above (D3:D6).

Please help me on this one I’m truly stuck and can’t get the formula to work right and I’m not sure if I need a Dynamic Array, Dynamic Index & Match, Dynamic SUMIFS or some other formula all together.

Thank You!

Dynamic Array Index Match Example.xlsx

Garrett

Try this ARRAY formula, copied down...
=INDEX($D$10:$O$13,MATCH($A3&$B3&$C3,$A$10:$A$13&$B$10:$B$13&$C$10:$C$13,0),MATCH($D$2,$D$9:$O$9,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

In D3, then copied down, a normal formula:

=INDEX($D$10:$O$13, MATCH(A3&"-"&B3&"-"&C3, INDEX($A$10:$A$13&"-"&$B$10:$B$13&"-"&$C$10:$C$13, 0), 0), MATCH(D$2, $D$9:$O$9, 0))

No, it's a normal formula due to the inner INDEX.

Happy to help and thanks for the feedback

Jerry, out of curiosity, is there a technical reason for adding the "-" in the concat?

It ensures that the lookup values are unique.

A quick example of how concatenation could cause a problem...

You want to lookup "X" in one column and "Y" in another column...

1...Z...X
2...XY...[empty cell]
3...X...Y

If you lookup A1&B1="XY" then row 2 meets that criteria but the correct result you want is on row 3.

So, if you use A1&"-"&B1 then you get the correct match on row 3.

good point, thanks Tony

Concatenation, especially on large ranges, is inefficient.

Nested IF functions are more efficient.

INDEX(range1,MATCH(1,IF(range2="this",IF(range3="that",1)),0))

Now, all I gotta do, is try and remember that

Yeah, there's a lot to remember!

I have 100's of files full of examples and notes.

I think Im getting to the stage where, if I put 1 more new thing in my brain, an old thing falls out the other side, and I get a message flashing up with "crawling ants" saying my resources are insufficient, close some files please