Dave Bruns

To do this, LOOKUP is configured as follows:

• Lookup values are ages in column C
• The lookup vector is the named range "age" (F5:F8)
• The result vector is the named range "group" (G5:G8)

With this setup, LOOKUP performs an approximate match on the...Read more

The LOOKUP function does an approximate match lookup in one range, and returns the corresponding value in another.

Although the table in this example includes both maximum and minimum values, we only need to use the minimum values. This is because when LOOKUP can't find a match, it will...Read more

First, you should know that Excel contains two functions, CLEAN and TRIM, that can automatically remove line breaks and extra spaces from text. For example to strip all line breaks from a cell, you could use:

=CLEAN(B5)

For a quick demo of...Read more

This example shows how the SUMIFS function can sometimes be used to "lookup" numeric values, as an alternative to more complicated multi-criteria lookup formulas. This approach is less flexible than more general lookup formulas based on ...Read more

In this example, the goal is to find the closest match to a target value entered in cell E5. Although it may not look like it, this is essentially a look-up problem. The easiest way to solve this problem is with the XLOOKUP function. However in...Read more

This formula relies on the helper table visible in the range B4:D10. Column B contains the six numbers we want as a final result. Column C contains the probability weight assigned to each number, entered as a percentage. Column D contains the cumulative probability, created with this formula in...Read more

This formula sits inside a small summary table with percentile values in column F and gender values in G4 and H4. Working from the inside out, the IF function is set up like this:

IF(Table[Gender]=G$4,Table[Score])

Here, each value in the...Read more

In this example, the goal is to create a dynamic reference to an Excel Table in a formula. In other words, create a formula that can refer to an Excel table by name as a variable. The easiest way to do this in Excel is to assemble the reference as a text value...Read more

This is a standard INDEX formula. The only trick to the formula is the use of a structured reference to return a range for the table headers:

Table1[#Headers]

This range goes into INDEX for the array argument, with the index value supplied...Read more