Multiple Criteria Lookup with Index and Match

[paradise2sr]

Hi,

I have used the index match formula for multiple criteria.

Please Login or Register  to view this content.

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.

[AliGW]

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.)

[paradise2sr]

Thanx for reminding me.I have modified the title.

[kvsrinivasamurthy]

May be this.
Pl see file with formulas.

[paradise2sr]

Can you kindly use purely Index & Match function as I am not fully comfortable of using aggregate function.

[AliGW]

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.

[Bo_Ry]

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)

[wk9128]

1. Pls change Cell W3 From "Pull 1 i.e F Column Data" To "Pull 2 i.e F Column Data"

2. Cell L7 Array Formula , Drag down and across (From L7 To AF8)

[paradise2sr]

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.

[wk9128]

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

[paradise2sr]

I mean to say without Array formula (i.e without CSE)

[wk9128]

Multiple Criteria needs to use array formulas. Don’t use array formulas. Maybe MS365 can help you, but I don’t have this version nor used unfamiliar functions. Otherwise, I should consider using VBA Code

[paradise2sr]

Momentarily I think I have to use Post #7 Sumifs function but I would appreciate be thankful if index match function would be used without any array formula due to its flexibility.

[kvsrinivasamurthy]

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.