Using the Offset and Match Functions

Question

JessA1965 Author

Level 1

10 points

Using the Offset and Match Functions

I have a Numbers table with a date in column A and a price in column B. In a second table, I use the Max and Min functions to find the maximum and minimal value in Table 1 and return the corresponding date to match the values. Based on a little research, I though the Offset and Match functions would return my desired results.

My formula in Table 2 C1 is

The formula works until I add another row (e.g., 9/30/21 in Table 1 A11 and $9.00 in B11). Table 2 B2 returns the correct Max value of $9.00 from Table 1::A11, but my formula in Table 1::C1 returns an error. I can change the Match search-where each time I add additional date and price (e.g., from Table 1::B3-B10 to Table 1:B3:B11) and the formula would return the correct values/date.

Initially, I though my I could modify the Match search-where from Table 1::B3:B10 to Table 1::$Price (search the entire column B), but that also returns an error, even if I delete row 11. I though deleting Table 1 row 1 where I initial had EOM in Table 1::A1 was causing the error with the updated formula, but that did not work either.

Any suggestion would be helpful.

Thanks,

Jess

Posted on Aug 3, 2021 2:56 PM

Reply

Answer 1

Badunit

Level 7

30,342 points

Aug 3, 2021 3:39 PM in response to JessA1965

Using the "whole column" reference of "Price" versus the range B2:B10 is the way to go. It will always include new rows. But you cannot use it as the starting cell for OFFSET in this formula. For that, use the single cell Table 1::B$1 or Table 1::A$1. If you use Table 1::A1 as the starting point for OFFSET there is no need to offset to the left one column.

Take a look at the result of MATCH in the formula. That is another problem. For example, say it finds the the max number in row 2. The result of MATCH will be 2. OFFSET(Table1::A1,2,0) will be the value in row 3, not row 2.

You might consider using INDEX instead of OFFSET.

=INDEX(Table 1::A,MATCH(B1,Table 1::B,0))

versus

=OFFSET(Table 1::A$1,MATCH(B1,Table 1::B,0)−1,0)

Or use XLOOKUP

=XLOOKUP(B1,Table 1::B,Table 1::A,"not found",0)

Reply

Answer 2

JessA1965 Author

Level 1

10 points

Aug 3, 2021 7:05 PM in response to Badunit

Badunit,

Thank you for your help. Also, I decided to go with your recommendation to use INDEX versus OFFSET. INDEX and MATCH achieves the same results and it appears the INDEX and MATCH combination are less volatile.

Reply