SOLVED

# Index/match formatting error?

Occasional Contributor

# Index/match formatting error?

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.

6 Replies

# Re: Index/match formatting error?

It might help if you attached a sample workbook

# Re: Index/match formatting error?

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

# Re: Index/match formatting error?

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.

# Re: Index/match formatting error?

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

# Re: Index/match formatting error?

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