Based on the values in Cells B77

Based on the values in Cells B77:B81, which function can automatically return the value in Cell C77? B 76 Sales 77
721 79
912 81
721
QUESTION 8/15 Based on the values in Cells B77:B81, which function can automatically return the value in Cell C77? B 76 Sales 77
721 79
912 81

The Correct Answer and Explanation is:

Correct Answer:
=INDEX(B77:B81, MATCH(B77, B77:B81, 0))

---

Explanation

To automatically return the value in Cell C77 based on the values in Cells B77:B81, we first need to understand what the question is asking.

Let’s break down the structure:

• Cell B77 contains: 721
• Cell B79 contains: 912
• Cell B81 contains: 721
• The range is: B77:B81
• C77 should return the value from B77 based on a function.

The question is: How can we use a formula to return the value in Cell B77 using a function that looks at values from B77:B81?

---

Understanding the Formula

The function:
=INDEX(B77:B81, MATCH(B77, B77:B81, 0))

This uses two key functions:

1. MATCH(lookup_value, lookup_array, match_type)
   • lookup_value = B77 → we are looking for the first occurrence of 721 in the range.
   • lookup_array = B77:B81 → this is the column we’re searching in.
   • match_type = 0 → exact match.
   • This returns 1 because the first 721 appears in B77, which is the first item in the range.
2. INDEX(array, row_num)
   • array = B77:B81
   • row_num = 1 (from the MATCH result)
   • INDEX will return the first value in the range, which is 721.

So, the formula effectively returns the same value as B77 using the combination of INDEX and MATCH — and does so automatically even if the values change later, as long as B77 still appears in B77:B81.

This is useful in spreadsheets to dynamically find and return values based on matching criteria, especially when dealing with duplicates or needing flexible lookups. It avoids hardcoding cell references and enhances automation in data processing.