Index/match formatting error?

Occasional Contributor




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.

6 Replies


It might help if you attached a sample workbook

All names, addresses and figure are completely made up which is why I have no redacted any, thank you for the reply too
best response confirmed by ExcelLearner2395 (Occasional Contributor)


Thanks. Try this formula in :




Fill down.

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


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