Forum Discussion
Why does this Index Match Work
INDEX('last week sales'!U:U,MATCH(1,('last week sales'!W:W=LARGE('last week sales'!W:W,B128))*(COUNTIF($C$127:C127,'last week sales'!U:U)=0),0))
This formula works perfectly, but like I don't understand why or how it works. Please provide an explanation so I can use it for other purposes. What does each component do?
You are to be commended for asking. and for seeking to learn. Normally I'd try to explain it, but that formula makes a lot of references to other sheets, and has functions embedded in functions, such that it would be pretty difficult to break it all apart in meaningful ways without access to the whole workbook.
And, in fact, since YOU have that access to the whole, you might consider doing that very thing: break the formula down into its various components, working from the inside out, understanding (for example) what
=(COUNTIF($C$127:C127,'last week sales'!U:U)=0)
yields
and then do the same with each of these in turn
=LARGE('last week sales'!W:W,B128)
=('last week sales'!W:W=LARGE('last week sales'!W:W,B128))
=MATCH(1,('last week sales'!W:W=LARGE('last week sales'!W:W,B128))*(COUNTIF($C$127:C127,'last week sales'!U:U)=0),0)
And then, finally, put it all together.
To help you along with more explanations of each function as such, here are links to very complete descriptions of each of them and how they work, how to use them.
When you're done, if you're in a position to do this, you might want to approach whoever created the spreadsheet in the first place and ask that in the future they provide documentation on their work, so that it can be both understood and (more importantly) maintained by other people, without having to start from scratch. That's just good development practice.