Multiple Criteria Lookup with Index and Match

Hi,

I have used the index match formula for multiple criteria.

=INDEX($B4:$G$12,MATCH($J7,$D$4:$D$12,0),MATCH(O$2&$I7,INDEX($B$4:$B$12&$A$4:$G$12,0),0))

pls let me know where I am wrong.

I have a data from A to H column .I want the result in L to U by matching inorder to pull data of C column by matching.

Secondly to pull data from F column in W to AF column row.

Matching criteria-
1.Row L2:U2 with column B
2.I column with Column A
3.J column with Column D

Other things are self explanatory.

Thanx in advance for consistent help.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

May be this.
Pl see file with formulas.

Originally Posted by kvsrinivasamurthy

May be this.
Pl see file with formulas.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Maybe try at L7

=SUMIFS($C$4:$C$99,$B$4:$B$99,L$2,$A$4:$A$99,$I7,$D$4:$D$99,$J7)

L3 row is not required to get match.I think the formula is ok but array formula slows down as I have to copied down to 3500 rows.

Without L3 cell , Cell L7 Array Formula , Drag down and across

HTML Code: 

Ref Post #5
Aggregate formulas are normal formulas, which are easy to enter.
Any way they are replaced by index and Match functions as ARRAY formulas.
In L7

=IFERROR(INDEX($C$4:$C$11,MATCH( $I7&L$2&$J7,$A$4:$A$11&$B$4:$B$11&$D$4:$D$11,0)),"")

IN W7

=IFERROR(INDEX($F$4:$F$11,MATCH( $I7&L$2&$J7,$A$4:$A$11&$B$4:$B$11&$D$4:$D$11,0)),"")

copied to range as required.