Advanced Excel Formula for Brand Share Lookup

Excel Formula Breakdown

The provided formula is designed to retrieve a value from a specified range in the worksheet 'EDN Brand Share Trended' based on certain criteria from the 'Trended Brand Share' sheet. It uses the INDEX and MATCH functions to perform lookups. Below is a breakdown of each component.

Formula

=INDEX('EDN Brand Share Trended'!$E$3:$AE$800, MATCH('Trended Brand Share'!$C$2 & VLOOKUP('Trended Brand Share'!$F3, STAGE!$O$4:$P$55, 2, FALSE), 'EDN Brand Share Trended'!$C$3:$C$800 & 'EDN Brand Share Trended'!$D$4:$D$800, 0), MATCH(VLOOKUP('Trended Brand Share'!$C$4, STAGE!$S$4:$T$6, 2, FALSE) & 'Trended Brand Share'!G$2, 'EDN Brand Share Trended'!$B$3:$B$800 & 'EDN Brand Share Trended'!$E$2:$AE$2, 0))

Components Explained

1. INDEX Function:

   • Syntax: INDEX(array, row_num, [column_num])
   • In this formula, it retrieves a value from the range 'EDN Brand Share Trended'!$E$3:$AE$800 based on the row and column numbers determined by the MATCH functions.

2. First MATCH Function:

   • Syntax: MATCH(lookup_value, lookup_array, [match_type])
   • The lookup value is constructed using:
     • 'Trended Brand Share'!$C$2: A value from the 'Trended Brand Share' sheet.
     • VLOOKUP('Trended Brand Share'!$F3, STAGE!$O$4:$P$55, 2, FALSE): Retrieves a related value from the 'STAGE' table based on the value in 'Trended Brand Share'!$F3.
   • The lookup array consists of a concatenated range: 'EDN Brand Share Trended'!$C$3:$C$800 & 'EDN Brand Share Trended'!$D$4:$D$800. This combines two column ranges into a single array for lookup.

3. Second MATCH Function:

   • Lookup value:
     • VLOOKUP('Trended Brand Share'!$C$4, STAGE!$S$4:$T$6, 2, FALSE): Retrieves a related value similar to the first VLOOKUP, based on 'Trended Brand Share'!$C$4.
     • 'Trended Brand Share'!G$2: Another value from the 'Trended Brand Share' sheet.
   • Lookup array: 'EDN Brand Share Trended'!$B$3:$B$800 & 'EDN Brand Share Trended'!$E$2:$AE$2, concatenating the necessary columns for the lookup.

Practical Example

Scenario

Suppose you have sales data listed in the 'EDN Brand Share Trended' sheet with various brands and their sales figures reflecting different time periods. You want to find the brand share for a specific brand and time period based on user selections in the 'Trended Brand Share' sheet.

Application

1. In 'Trended Brand Share'!C2: Contains the brand name.
2. In 'Trended Brand Share'!F3: Contains a reference ID to look up additional information.
3. In 'Trended Brand Share'!C4: Contains another reference to identify a row in the STAGE table for lookup.
4. In 'Trended Brand Share'!G2: Contains a time frame or another dimension you wish to consider.

When implemented, the formula efficiently retrieves the corresponding brand share from the 'EDN Brand Share Trended' sheet by matching user-defined criteria.

Conclusion

This comprehensive formula harnesses the power of INDEX and MATCH, combined with VLOOKUP, to perform a multi-dimensional lookup based on data spread across multiple sheets. The use of concatenation allows for more flexibility in matching and ensures relevant values are retrieved accurately based on the defined criteria. For further mastery of such techniques, consider engaging with resources and courses from the Enterprise DNA Platform.