Usage & tips

Meet LAMBDA(). Enter the formula wrapped in a LAMBDA-function, place it in a defined name, and call upon the calculation from different places in your spreadsheet.

The combination of INDEX and MATCH, as well as the VLOOKUP function are well known methods for search in Excel. To our great frustration, the latter is used a lot more often, while the combination of INDEX and MATCH is less error prone and a lot more efficient.

The MATCH function in Excel looks for a certain value in a specified lineair cell range and returns the relative position of that value within this range.

Long ago, Excel was not as clever and advanced as it is today, and the so-called array functions are an inheritance of this past. However, most people don’t know about them, and don’t understand them. This is perhaps because of the different nature or the strange method to activate these formulas (CTRL-SHIFT ENTER instead of just ENTER).

XLOOKUP is the newest lookup function in Excel. It joins INDEX & MATCH, VLOOKUP, and HLOOKUP as the newest weapon in the arsenal of formulas used to masterfully locate and supply data across models.

HLOOKUP is a function that makes Excel search for a certain value in a row (the so called ‘table array’), in order to return a value from a different row in the same column.