We often need to find cell references to use in our formulas. In this post, we will look at several such functions: ADDRESS, ROW, COLUMN, OFFSET, and INDIRECT. We mention INDEX(MATCH) but cover them in other posts. For simplicity, in this post, we use the “A1” format of cell references throughout. The other option, the…
For some lookups, the INDEX and MATCH functions used together may be the solution. Together, they are more robust and versatile than VLOOKUP, HLOOKUP, and LOOKUP. MATCH returns the position of the matched item in a one-dimensional list. It can do an exact or approximate match. MATCH(lookup_value, lookup_array, 0) does an exact match. INDEX returns…
Do you need to pick out a name from a list based on some data? It might be the best-performing employee or department. Or the machine next in need of a service. Or the supplier whose concrete mix was the most consistent, or many others. This isn’t specifically an engineering application, but of general use:…