IFERROR + INDEX + MATCH Formula

Formula:

=IFERROR(INDEX(return_range, MATCH(1, (criteria1_range = criteria1) * (criteria2_range = criteria2) * (criteria3_range = criteria3), 0)), "Not Found")

Explanation:

1. The INDEX function is used to retrieve a value from a specified range.
2. The MATCH function is used to find the position of a specified value within a range.
3. The (criteria1_range = criteria1) * (criteria2_range = criteria2) * (criteria3_range = criteria3) part creates an array of TRUE/FALSE values, where TRUE represents a match for the respective criteria.
4. By multiplying the arrays together, we create a final array with 1s where all criteria are met and 0s where there is a mismatch.
5. The MATCH function then looks for a 1 within this array to find the row or column where all criteria are met.
6. The IFERROR function is used to handle cases where no match is found. It returns "Not Found" instead of an error.
7. Replace return_range, criteria1_range, criteria2_range, criteria3_range, criteria1, criteria2, and criteria3 with the appropriate ranges and criteria for your data.

Practical Example:

Let's say you have a table of sales data with columns for region, product, and sales amount. You want to retrieve the sales amount for a specific region, product, and month.

Assuming the table is in the range A2:D10, with region in column A, product in column B, sales amount in column D, and the criteria are in cells F2, G2, and H2, you can use the following formula:

=IFERROR(INDEX(D2:D10, MATCH(1, ($A$2:$A$10 = F2) * ($B$2:$B$10 = G2) * ($C$2:$C$10 = H2), 0)), "Not Found")

This formula will return the sales amount for the specified region, product, and month, or "Not Found" if no match is found.

Remember to adjust the ranges and criteria in the formula to match your actual data.