# INDEX MATCH right to left?

#### mbrown2626

##### New Member

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

#### sambharris6

##### New Member
Use XLOOKUP, no need to right to left

#### hajiali

##### Active Member
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
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

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

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)

#### hajiali

##### Active Member
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
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.

Replies
6
Views
134
Replies
3
Views
104
Replies
6
Views
103
Replies
2
Views
80
Replies
5
Views
36

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.

### Which adblocker are you using?

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

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