Index Matching with Multiple Criteria and Greater Than

[El_Dee_El]

Hello,

Excel noob when it comes to Index Match and first time posting. TIA!

I am creating a spreadsheet to model portfolio trades based off of target trade amounts. I'm trying to prioritize the allocation of certain "Stock Types".

I'm wondering how I could retrieve a specific figure based off of multiple criteria and a greater than parameter.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Stock Type[/TD]
[TD]Growth[/TD]
[TD]Bond[/TD]
[TD]Growth[/TD]
[TD]Real[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Stock Name[/TD]
[TD]StockA[/TD]
[TD]StockB[/TD]
[TD]StockC[/TD]
[TD]StockD[/TD]
[TD][/TD]
[TD]Stock Type[/TD]
[TD]Growth[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]First Trade[/TD]
[TD]1200[/TD]
[TD]3000[/TD]
[TD]1300[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD]Trade[/TD]
[TD]Second Trade[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Second Trade[/TD]
[TD]0[/TD]
[TD]3000[/TD]
[TD]1300[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Third Trade[/TD]
[TD]0[/TD]
[TD]3000[/TD]
[TD]0[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In summary, I'd like to determine how to pull the "Second Trade" amount based off of the "Stock Type" being classified as "Growth" and the value being greater than zero. I'd like the formula to return 1300 from D3 based off of H2 and H3. Thanks!

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Stock Type[/TD]
[TD]Growth[/TD]
[TD]Bond[/TD]
[TD]Growth[/TD]
[TD]Real[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Stock Name[/TD]
[TD]StockA[/TD]
[TD]StockB[/TD]
[TD]StockC[/TD]
[TD]StockD[/TD]
[TD][/TD]
[TD]Stock Type[/TD]
[TD]Growth[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]First Trade[/TD]
[TD]

1200​

[/TD]
[TD]

3000​

[/TD]
[TD]

1300​

[/TD]
[TD]

5000​

[/TD]
[TD][/TD]
[TD]Trade[/TD]
[TD]Second Trade[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Second Trade[/TD]
[TD]

0​

[/TD]
[TD]

3000​

[/TD]
[TD]

1300​

[/TD]
[TD]

5000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Third Trade[/TD]
[TD]

0​

[/TD]
[TD]

3000​

[/TD]
[TD]

0​

[/TD]
[TD]

5000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1300​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


G6=SUMPRODUCT(($A$3:$A$5=H3)*(B1:E1=H2)*(B3:E5<>0)*(B3:E5))

 

[Eric W]

Welcome to the MrExcel Board!

How about:

Excel 2012

	A	B	C	D	E	F	G	H
Stock Type	Growth	Bond	Growth	Real
Stock Name	StockA	StockB	StockC	StockD	Stock Type	Growth
First Trade	Trade	Second Trade
Second Trade	Amount
Third Trade

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1200[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1300[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]{=MAX(B3:E5*(B1:E1=H2)*(A3:A5=H3))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


You can also do it with this non-array entered formula:

=AGGREGATE(14,6,B3:E5/(B1:E1=H2)/(A3:A5=H3),1)

It really is an array formula under the covers, but you don't need to use the Control+Shift+Enter.

 

[Eric W]

It's probably worth noting the differences between Marziotullio's formula and mine. If you have multiple matching entries, his will return the sum of them. Mine (both of them) will return the maximum of the matching values. For example, if B4 is 200 instead of 0, his formula returns 1500, and mine 1300. So choose the one that suits your needs.

 

[El_Dee_El]

Just getting around to testing this out. Thanks for the responses!

I'm not looking for the sum of several values. Eric's seems closer to what I need.

I should provide some more information. Each trade or Row is a result of the preceeding row. Therefore, the formula cannot pull from the entire range of B3:B5. For example, I have a cell specifying the first "First Trade" somewhere else. That first trade recognizes that there is a "Growth" with StockA and submits a trade for that reducing its availability to zero when it comes to the "Second Trade". The second trade should pull the next growth stock available which should be D4.

I just realized there was a type saying that the second trade should pull D3 instead of D4. I will go back and edit the original post.

All in all, how would I write the formula to focus on a row vs. the entire range? I tried to edit the aggregate formula but it gave me an error.