Combine 3 different index-match functions but referencing 1 specific helper cell

derman0524

New Member
Joined
Apr 22, 2024
Messages
4
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
  2. MacOS
  3. Web
Hi there, I've been struggling to get the index-match function to work properly with more than 1 index match.

Basically, below are 3 different 'rows', where each column is 1 week across a full month. In the Name tab at the bottom right, I want to output the correct text above the MAX value in this entire array. I'm able to do single index match but I can't seem to correctly add multiple index arrays when they're on different row numbers.

Currently: =INDEX(E20:I20,MATCH($J$21,E21:I21,0)) which outputs Vale, as it correctly found the matched the max value with the index row array just above it, but I can't seem to figure out how to make add multiple rows to the index array if they're on different rows.

My other thinking is to have some sort of index nested function with some if statements to output the MAX value and attach the Name to that max value across the entire array.

Let me know. Thanks!

1713798233760.png
 

Attachments

  • Screenshot 2024-04-22 at 12.37.46 PM.png
    Screenshot 2024-04-22 at 12.37.46 PM.png
    42.1 KB · Views: 11

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Maybe this.
Book1
ABCDEF
1 CCr SNC
2$ 20,000$ 50,000 Sum $ 13,570,000
3 Kennecott Vale Kevin Max $ 8,000,000
4$ 500,000$ 2,000,000$ 3,000,000 Name Valerie
5 Valerie
6$ -$ -$ 8,000,000
Sheet3
Cell Formulas
RangeFormula
F2F2=SUM(A1:D6)
F3F3=MAX(A1:D6)
F4F4=LET(w,WRAPROWS(TOCOL(A1:D6,,TRUE),2),FILTER(CHOOSECOLS(w,1),CHOOSECOLS(w,2)=MAX(w)))
 
Upvote 0
Solution
Maybe this.
Book1
ABCDEF
1 CCr SNC
2$ 20,000$ 50,000 Sum $ 13,570,000
3 Kennecott Vale Kevin Max $ 8,000,000
4$ 500,000$ 2,000,000$ 3,000,000 Name Valerie
5 Valerie
6$ -$ -$ 8,000,000
Sheet3
Cell Formulas
RangeFormula
F2F2=SUM(A1:D6)
F3F3=MAX(A1:D6)
F4F4=LET(w,WRAPROWS(TOCOL(A1:D6,,TRUE),2),FILTER(CHOOSECOLS(w,1),CHOOSECOLS(w,2)=MAX(w)))
that worked! tbh I don't really understand what you did but it works well. Can you explain in a few words the solution please?
 
Upvote 0
It turns your data into a 2-column table and then filters on the max value.
Book1
HI
2 CCr 20000
3 Kennecott 500000
400
5 SNC 50000
6 Vale 2000000
700
800
900
10 Valerie 8000000
1100
12 Kevin 3000000
1300
Sheet3
Cell Formulas
RangeFormula
H2:I13H2=WRAPROWS(TOCOL(A1:D6,,TRUE),2)
Dynamic array formulas.
 
Upvote 0
okay thank you for the explanation. I'm now getting a SPILL error when I try to do the same concept on an array below it. Any ideas?
It turns your data into a 2-column table and then filters on the max value.
Book1
HI
2 CCr 20000
3 Kennecott 500000
400
5 SNC 50000
6 Vale 2000000
700
800
900
10 Valerie 8000000
1100
12 Kevin 3000000
1300
Sheet3
Cell Formulas
RangeFormula
H2:I13H2=WRAPROWS(TOCOL(A1:D6,,TRUE),2)
Dynamic array formulas.
 
Upvote 0
SPILL error means there is stuff in the cells the array is trying to spill out to. Need to make space.
 
Upvote 0
that worked! tbh I don't really understand what you did but it works well. Can you explain in a few words the solution please?
The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.
 
Upvote 0

Forum statistics

Threads
1,223,956
Messages
6,175,613
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top