SOLVED

Index/match formatting error?

Copper Contributor

 

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

@ExcelLearner2395 

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 (Copper Contributor)
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.

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

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

1 best response

Accepted Solutions
best response confirmed by ExcelLearner2395 (Copper Contributor)
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.

View solution in original post