SOLVED

Index/match formatting error?

%3CLINGO-SUB%20id%3D%22lingo-sub-2387994%22%20slang%3D%22en-US%22%3EIndex%2Fmatch%20formatting%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387994%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20on%20Windows%2010%20and%20Office%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20I'm%20making%20a%20formatting%20error%20when%20trying%20to%20continue%20a%20formula%20down%20a%20list.%20The%20current%20formula%20I'm%20using%20is%20%22%20%3DINDEX(M5%3AO5%2CMATCH(A4%2CL5%3AL2965%2C0)%2CMATCH(A4%2CQ4%3AQ2694%2C0)%2CMATCH(MAX(R4%3AT4)%2CR4%3AT4%2C0))%20%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20get%20find%20the%20max%20result%20from%20colum%20R%20S%20T%20for%20each%20row%2C%20this%20will%20then%20match%20with%20a%20product%20ID%20from%20M%20N%20O%20and%20this%20is%20the%20figure%20I'm%20wanting%20in%20my%20cell.%20I%20have%20the%20first%20value%20correct%20but%20whenever%20I%20try%20and%20extend%20the%20formula%20down%20the%20list%20i%20get%20%22%23REF!%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2387994%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388106%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2Fmatch%20formatting%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063823%22%20target%3D%22_blank%22%3E%40ExcelLearner2395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20might%20help%20if%20you%20attached%20a%20sample%20workbook%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388130%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2Fmatch%20formatting%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388131%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2Fmatch%20formatting%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388131%22%20slang%3D%22en-US%22%3EAll%20names%2C%20addresses%20and%20figure%20are%20completely%20made%20up%20which%20is%20why%20I%20have%20no%20redacted%20any%2C%20thank%20you%20for%20the%20reply%20too%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388635%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2Fmatch%20formatting%20error%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063823%22%20target%3D%22_blank%22%3E%40ExcelLearner2395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks.%20Try%20this%20formula%20in%20%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24M%244%3A%24O%242964%2CMATCH(A3%2C%24L%244%3A%24L%242964%2C0)%2CMATCH(MAX(INDEX(%24R%244%3A%24T%242964%2CMATCH(A3%2C%24Q%244%3A%24Q%242694%2C0)%2C))%2CINDEX(%24R%244%3A%24T%242964%2CMATCH(A3%2C%24Q%244%3A%24Q%242694%2C0)%2C)%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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))