INDEX MATCH Array formula Help

Hi Excel Forum Team,

Please note, I have readdressed and have tried to clarify my formula question and have also updated my example spreadsheet link just below, so I hope this helps.

Contained in the example spreadsheet link below, in the “Results” titled column (H), I have INDEX MATCH Array formulas that work perfectly when scanning through the primary “Inventory A” title column (E), but now I need the “Results” titled column (H) to also process a separate second set of data. The two separate sets of data, the 1st contained in the “Inventory A” titled column (E) and 2nd contained in the “Inventory B” titled column (F) and the current formulas in the “Results” titled column (H) returns only those items from the “Inventory A” title column (E).

Then, based on the two constraints the “WBS” (C3) and the “Vendor Name” (D3) prevent the formulas in the “Results” titled column (H) from processing and returning any of the data from row 12 to 23 (below the dotted line).

Also, the formula in the “Results” titled column (H) excludes any blank cells and places the first returned answer at the very top of the “Results” titled column (H).

Lastly, I need the formula to have the ability to sort the order of the “Results” titled column (H) by having the primary “Inventory A” items first and on top of the secondary “Inventory B” items last.

For reference in your help fixing my formula, I added in a “Sample” titled column (J) in order to show how the formulas contained in the “Results” titled column (H) should be returning as the answers. I also colored in the cells that contain the similar inventory data to formula return items in order to better show their relationships.

Thank you for your time and help.

INDEX MATCH Array Formula Example (Rev.1).xlsx

Garrett

Check the attached file...
and tell is that what you were trying to achieve...

Formula:

Please Login or Register  to view this content.

Please see my revised formula question and updated example spreadsheet link in the first post above.

I hope this helps...

Garrett

Please see my revised formula question and updated example spreadsheet link in the first post above.

I hope this helps...

Garrett

Cheers..
Finally I got what you want..

Formula:

Please Login or Register  to view this content.

one helper column used ..
Have a look..

provide the expected results manually in your sample file

Here's a tip; rather than editing your first post you should just post a new post. If you change the first post after a lot of people have already answered, the thread just becomes a mess.

see the attached file

Please see attached file with formula in H6

Hi Garrett,
It seems that your requirements change faster than we can write formulas for you. Maybe you should start a new thread so more folks could get involved.

Cheers,

Hi AlKey,

Yes, I'm sorry about that. The data I'm going through is in the tens of thousands and as I run the updated formulas down the entire row I seem to have another hangup where I find another condition that pops up.

I know this is the last modification to your formula that I'll need because the last bit of data is just pending on this change and it will be all good!

I really appreciate all the help you have given me and all you have done.

Through your help, in my next cost reporting period that starts on Oct.1, I'll be able to run through all the project costs data much faster an be able to see and spend time with my family at night after work rather than sitting in front of that computer at work for those long hours at night working.

Thank you again!

Garrett