CODEWITHSUNDEEP
excel
Kevin McGuire
Kevin McGuire

Reputation: 1

Offset function using match function

I am calculating the MAX value of (B17:M17,B22:M22) in cell L6. I need to find the corresponding cell two rows above the MAX for the range. I tried the formula below, but its not working. Any ideas?

=OFFSET(MATCH(L6, (B17:M17,B22:M22),0), -2,0)

Upvotes: 0

Views: 400

Answers (1)

Qualia Communications
Qualia Communications

Reputation: 715

This works for me:

Put the following formula in N6:

=OFFSET($B$17,ROW(IF(ISNA(MATCH(L6,$B$17:$M$17,0)),$B$22,$B$17))-ROW($B$17)-2,IFNA(MATCH(L6,$B$17:$M$17,0),MATCH(L6,$B$22:$M$22,0))-1)

enter image description here

Explanation

As requested, here is a in-depth explanation

Range names

I will use the following "range names" in the following explanation:

  • current_max = $L$6 (the cell that contains the MAX formula)
  • start_range_1 = $B$17 (the first cell of the first range)
  • range_1 = $B$17:$M$17 (the first range)
  • start_range_2 = $B$22 (the first cell of the second range)
  • range_2 = $B$22:$M$22 (the second range)

The formula

=OFFSET($B$17,ROW(IF(ISNA(MATCH(L6,$B$17:$M$17,0)),$B$22,$B$17))-ROW($B$17)-2,IFNA(MATCH(L6,$B$17:$M$17,0),MATCH(L6,$B$22:$M$22,0))-1)

Let's break it into pieces:

=OFFSET(*anchor*, *row_offset*, *column_offset*)

  • anchor = start_range_1 is the anchor / starting point of the first range.
  • row_offset = ROW(IF(ISNA(MATCH(current_max,range_1,0)), start_range_2, start_range_1))-ROW(start_range_1)-2

The IF statements checks (with MATCH) whether the current_max is included in range_1. If it is not (ISNA), it returns the first cell of the second range (start_range_2), otherwise it returns the first cell of the first range (start_range_1). This result is wrapped in a ROW statement to identify the row number of that cell. As the OFFSET formula uses start_range_1 as anchor, its row number is subtracted from the result. As the questioner wanted to have the result of two rows above the cell that contains the maximum value, additional 2 (rows) are subtracted.

  • column_offset = IFNA(MATCH(current_max,range_1,0),MATCH(current_max,range_2,0))-1

This time, we check again, if the current_max belongs to range_1. MATCH either returns the respective column or NA. If it returned NA, another MATCH check is initiated (via ISNA) to identify the column in range_2 that featured the current maximum value. As the anchor is the first column, we need to subtract one column from the result.

Upvotes: 1

Related Questions