INDEX MATCH right to left?

mbrown2626

New Member
Joined
Mar 22, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
1616447096358.png


Formula in Cell AY5

=IFERROR(INDEX($AZ$3:$BM$3,MATCH(MAX(AZ5:BM5),AZ5:BM5,0)),"")

As you can see, Cells BA5 and BB5 have the same value which are both the "MAX". how do I get the value in AY5 to return M02 vs M01? Basically I have multiple cells with the max value so I need the last one in the row that is the max.
 

Attachments

  • 1616446999847.png
    1616446999847.png
    5.4 KB · Views: 4

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
if you need same row you can use CONCATENATE( ) in AY5 or if you like them separate use the other method.

Book1
AYAZBABBBCBDBE
3M00M01M02M03M04M05
5M01 M021/1/211/2/211/2/21
6M01
7M02
Sheet1
Cell Formulas
RangeFormula
AY5AY5=CONCATENATE(INDEX($AZ$3:$BM$3,MATCH(MAX($AZ$5:$BM$5),$AZ$5:$BM$5,0))," ",INDEX($AZ$3:$BM$3,MATCH(MAX($AZ$5:$BM$5),$AZ$5:$BM$5,0)+1))
AY6:AY7AY6=IFERROR(INDEX($AZ$3:$BM$3,MATCH(MAX($AZ$5:$BM$5),$AZ$5:$BM$5,0)+ROWS($AY$3:AY3)-1),"")
 
Upvote 0
Hi,

If your Row 5 values are in Ascending order:

Book3.xlsx
AYAZBABBBCBDBE
3M00M01M02M03M04M05
5M021/1/20211/2/20211/2/2021
Sheet854
Cell Formulas
RangeFormula
AY5AY5=LOOKUP(9^9,AZ5:BM5,AZ3:BM3)
 
Upvote 0
if you need same row you can use CONCATENATE( ) in AY5 or if you like them separate use the other method.

Book1
AYAZBABBBCBDBE
3M00M01M02M03M04M05
5M01 M021/1/211/2/211/2/21
6M01
7M02
Sheet1
Cell Formulas
RangeFormula
AY5AY5=CONCATENATE(INDEX($AZ$3:$BM$3,MATCH(MAX($AZ$5:$BM$5),$AZ$5:$BM$5,0))," ",INDEX($AZ$3:$BM$3,MATCH(MAX($AZ$5:$BM$5),$AZ$5:$BM$5,0)+1))
AY6:AY7AY6=IFERROR(INDEX($AZ$3:$BM$3,MATCH(MAX($AZ$5:$BM$5),$AZ$5:$BM$5,0)+ROWS($AY$3:AY3)-1),"")
Problem is I need the result to be in cell AY5 as opposed to be in a different row. I have 1000's of lines with data in them and I need column AY to be the the result for that rows data.....
 
Upvote 0
Hi,

If your Row 5 values are in Ascending order:

Book3.xlsx
AYAZBABBBCBDBE
3M00M01M02M03M04M05
5M021/1/20211/2/20211/2/2021
Sheet854
Cell Formulas
RangeFormula
AY5AY5=LOOKUP(9^9,AZ5:BM5,AZ3:BM3)
OK, that works as my data will be in Ascending....so how does the 9^9 work? What is that telling the lookup to do?
 
Upvote 0
Problem is I need the result to be in cell AY5 as opposed to be in a different row. I have 1000's of lines with data in them and I need column AY to be the the result for that rows data.....

In that case , add Absolute referencing for Row 3:

Book3.xlsx
AYAZBABBBCBDBE
3M00M01M02M03M04M05
5M021/1/20211/2/20211/2/2021
6M031/2/20211/3/2021
Sheet854
Cell Formulas
RangeFormula
AY5:AY6AY5=LOOKUP(9^9,AZ5:BM5,AZ$3:BM$3)
 
Upvote 0
Solution
how many times the max date will be repeated in each row, is it different for each row, is there a max for each row?
 
Upvote 0
OK, that works as my data will be in Ascending....so how does the 9^9 work? What is that telling the lookup to do?

In some cases, I would use 9.99999999999999E+307 rather than 9^9, and sometimes 2^15, it really depends on the situation, but you'll never go wrong with the 9.99999999999999E+307, it's sometimes referred to as the "Big Num", supposedly the largest number Excel can handle.

So the LOOKUP is trying to find this Huge number, but can't find it, so it'll return the very Last value.
In this case, since we're dealing with Dates, 9^9 provides over 1 Million years of dates, more than plenty, I'd say.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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