Index/match formatting error?

[ExcelLearner2395]

Hi,

I'm on Windows 10 and Office 365.

I believe I'm making a formatting error when trying to continue a formula down a list. The current formula I'm using is " =INDEX(M5:O5,MATCH(A4,L5:L2965,0),MATCH(A4,Q4:Q2694,0),MATCH(MAX(R4:T4),R4:T4,0)) ".

I'm trying to get find the max result from colum R S T for each row, this will then match with a product ID from M N O and this is the figure I'm wanting in my cell. I have the first value correct but whenever I try and extend the formula down the list i get "#REF!".

Any help is greatly appreciated.

[#2]

@ExcelLearner2395 

It might help if you attached a sample workbook

[#3]

@Hans Vogelaar 

[#4]

All names, addresses and figure are completely made up which is why I have no redacted any, thank you for the reply too

[#5 · Accepted solution]

@ExcelLearner2395 

Thanks. Try this formula in :

=INDEX($M$4:$O$2964,MATCH(A3,$L$4:$L$2964,0),MATCH(MAX(INDEX($R$4:$T$2964,MATCH(A3,$Q$4:$Q$2694,0),)),INDEX($R$4:$T$2964,MATCH(A3,$Q$4:$Q$2694,0),),0))

Fill down.

[#6]

You're an absolute superstar thank you! It's worked perfectly except for the final 140 lines but I have no idea why

[#7]

@ExcelLearner2395 

Sorry, that's due to a typo. It should be

=INDEX($M$4:$O$2964,MATCH(A3,$L$4:$L$2964,0),MATCH(MAX(INDEX($R$4:$T$2964,MATCH(A3,$Q$4:$Q$2964,0),)),INDEX($R$4:$T$2964,MATCH(A3,$Q$4:$Q$2964,0),),0))