Dave Bruns

Consider a simple dynamic reference to Sheet2 using the INDIRECT in a formula like this:

=INDIRECT($B$5&"!"&"A1")

If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet....Read more

The goal is to retrieve the value of a cell at a given row and column location, which are entered in cells G5 and G4, respectively. There are several ways to go about this, depending on your needs. See below for options.

ADDRESS and INDIRECT

In the example worksheet, the...Read more

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