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: 3

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

hajiali

Active Member
Joined
Sep 8, 2018
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
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),"")
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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)
 

mbrown2626

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

ADVERTISEMENT

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

mbrown2626

New Member
Joined
Mar 22, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

hajiali

Active Member
Joined
Sep 8, 2018
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
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?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,939
Messages
5,656,043
Members
418,268
Latest member
Vegas01

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
Top