Excel Formula for Matching Words and Figures

Objective

To create a formula that checks if the words in the range F3:F12 match the words in A1:A10. If they match, the formula should return the corresponding figure from the range H3:H12 that matches the figure in B12:B20.

Formula Construction

Excel Formula

To achieve this, you can use an array formula combined with INDEX, MATCH, and IFERROR. Here is the formula:

=IFERROR(INDEX(H3:H12, MATCH(TRUE, ISNUMBER(MATCH(F3:F12, A1:A10, 0)), 0)), "")

Explanation of the Formula

1. MATCH(F3:F12, A1:A10, 0):

   • This portion checks for each word in the range F3:F12 to find a match within A1:A10.
   • It returns an array where matches return their respective positions and non-matches return #N/A.

2. ISNUMBER(...):

   • Encapsulates the MATCH function. This converts the output from MATCH into a boolean array (TRUE for matches and FALSE for non-matches).

3. MATCH(TRUE, ..., 0):

   • This overall MATCH identifies the first occurrence of TRUE in the boolean array produced by ISNUMBER, effectively locating the first match.

4. INDEX(H3:H12, ...):

   • After finding the position (row number) of the first match, INDEX retrieves the corresponding value from the range H3:H12.

5. IFERROR(..., ""):

   • Wraps the entire formula in IFERROR to ensure that if no matches are found, the result is an empty string instead of an error.

Practical Example

Data Setup

Results

• If F3 contains "Apple", the formula will retrieve the corresponding value from H3, which is 100.
• If F4 contains "Orange", the value retrieved will be 150.
• If F5 contains "Grape", the value will be 250.
• If no matches occur, the formula will return an empty string.

Conclusion

This structured approach provides a robust way to match words in one range with another and retrieve corresponding figures based on those matches. Users can apply this formula directly within their Excel sheets to address similar data analysis tasks. For further learning and enhancement of data skills, consider exploring offerings available on the Enterprise DNA Platform.