0

This is a question about Apple Numbers spreadsheet program.

Suppose I have two sheets: Benchmark and Training. In the Benchmark sheet, I track a benchmark value over time:

| Date       | Benchmark |
| ---------- | --------- |
| 2017-12-25 |     120.3 |
| 2018-01-01 |     121.4 |
| 2018-01-08 |     123.0 |

In the Training sheet, I record training values, also keyed by date, that relate to the benchmark value:

                            v--- column I want

| Date       | Actual | ... % of Benchmark |
| ---------- | ------ |     -------------- |
| 2017-12-26 |  119.5 |             -0.66% |   (benchmark: 120.3)
| 2017-12-27 |  120.0 |             -0.25% |   (benchmark: 120.3)
| 2018-01-09 |  122.1 |             -0.73% |   (benchmark: 123.0)

In words, for each row of the Training sheet, I want to determine the benchmark value that was taken most recently as of the training entry. On both 2017-12-26 and 2017-12-27, the most recent benchmark was as of 2017-12-25. On 2018-01-09, the most recent benchmark became 2019-01-08.

Is there a name for this type of reference? And how can I accomplish this in Apple Numbers?

-1

The LOOKUP function works for this.

In the Training sheet, use this formula to get the Benchmark value:

LOOKUP(A2, Benchmark::Table 1::Date, Benchmark::Table 1::Benchmark)

        ^
        \-- reference to the Date cell in your current row

The Apple Numbers documentation for LOOKUP is ambiguous about how this function behaves when the search-for value is not found, but in my testing, this function behaves just like its Excel counterpart.

The Excel documentation for LOOKUP says:

If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

And that's exactly what's needed here.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.