Hi, I have this data:
I want to get the result as shown in the "RESULT" table based on the reference in the "DATA" table. I want to retrieve the data with the highest value using a formula with 3 criteria and get the result as shown in column "WH" (column M). The result of the formula is the same as I want. But, with the same criteria, I'm having a hard time getting a formula so that the results are as shown in the "Rack" column (column L) where the highest value comes from. Can anyone help me?. Thank you
TEST 1.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | |||
2 | DATA | RESULT | ||||||||||||
3 | ||||||||||||||
4 | SKU | Title | Category | Contain Box | Rack | WH | SKU | Title | Category | Rack | WH | |||
5 | 111201.067 | Data 1 | TK | 210 | RA13 | 420 | 111201.067 | Data 1 | TK | RA13 | 420 | |||
6 | 111201.069 | Data 2 | TK | 210 | RA13 | 210 | 111201.069 | Data 2 | TK | RA13 | 210 | |||
7 | 111201.081 | Data 3 | TK | 40 | B32 | 40 | 111201.081 | Data 3 | TK | B32 | 40 | |||
8 | 111201.082 | Data 4 | TK | 60 | B32 | 60 | 111201.082 | Data 4 | TK | B32 | 60 | |||
9 | 111201.083 | Data 5 | TK | 30 | A27 | 30 | 111201.083 | Data 5 | TK | A27 | 30 | |||
10 | 111201.084 | Data 6 | TK | 27 | A27 | 27 | 111201.084 | Data 6 | TK | A27 | 27 | |||
11 | 111201.085 | Data 7 | TK | 110 | A27 | 110 | 111201.085 | Data 7 | TK | A27 | 200 | |||
12 | 111201.085 | Data 7 | TK | 200 | A27 | 200 | 111201.086 | Data 8 | TK | A27 | 200 | |||
13 | 111201.085 | Data 7 | TK Total | 310 | 111201.087 | Data 9 | TK | RA25 | 250 | |||||
14 | 111201.086 | Data 8 | TK | 200 | A27 | 200 | 111201.089 | Data 10 | TK | RB05 | 260 | |||
15 | 111201.086 | Data 8 | TK | 90 | A27 | 90 | 111201.091 | Data 11 | TK | A07 | 250 | |||
16 | 111201.086 | Data 8 | TK Total | 290 | ||||||||||
17 | 111201.087 | Data 9 | TK | 250 | RA25 | 250 | ||||||||
18 | 111201.089 | Data 10 | TK | 260 | RB05 | 260 | ||||||||
19 | 111201.091 | Data 11 | TK | 250 | A07 | 250 | ||||||||
20 | 111201.091 | Data 11 | TK | RA14 | 250 | |||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M5:M15 | M5 | =MAX(IF($B$5:$B$20=I5,IF($C$5:$C$20=J5,IF($D$5:$D$20=K5,$G$5:$G$20)))) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
I want to get the result as shown in the "RESULT" table based on the reference in the "DATA" table. I want to retrieve the data with the highest value using a formula with 3 criteria and get the result as shown in column "WH" (column M). The result of the formula is the same as I want. But, with the same criteria, I'm having a hard time getting a formula so that the results are as shown in the "Rack" column (column L) where the highest value comes from. Can anyone help me?. Thank you