I am stuck on these last few questions and Can’t seem to figure out the Indirect and IFERROR.

/in /by
On the InventoryAudit worksheet, the cells in range A3:D17 need to be filled in based on the category that is listed in cell B1, “Massage”. The table will then contain the information about each product within the listed category. Using named ranges already created in the Inventory worksheet, complete the following:

In cell B3, create a formula to retrieve the item within each category in cell B1. Enter an INDIRECT function to return the item numbers by category. The function will reference cell $B$1 (absolute) and index the value of the intersection of row 3 (A3) and column 1.
Nest the function in an IFERROR function to return a blank cell if no items exist within the category.
On the InventoryAudit worksheet in cell C3, create a formula to retrieve the projected sales for each item within the category in cell B1. Enter an INDIRECT function to return the item numbers by category. The function will reference cell $B$1 (absolute) and index the value of the intersection of row 3 (A3) and column 2. Nest the function in an IFERROR function to return a blank cell if no items exist within the category.

 

To accomplish the task described, you can use the following formula in cell B3 of the InventoryAudit worksheet:

“`
=IFERROR(INDIRECT(“Inventory!” & INDEX(Category, MATCH($B$1, CategoryList, 0)) & “3”), “”)
“`

This formula assumes that you have a named range called “Category” in the Inventory worksheet that contains the range of cells with category names (e.g., A2:A10), and a named range called “CategoryList” that contains the range of cells with the category names in cell A1 to retrieve the correct column number.

Here’s a breakdown of the formula:

1. `MATCH($B$1, CategoryList, 0)` finds the position of the category name in cell B1 within the named range “CategoryList”.
2. `INDEX(Category, MATCH($B$1, CategoryList, 0))` returns the corresponding category name from the named range “Category” based on the matched position.
3. `INDIRECT(“Inventory!” & INDEX(Category, MATCH($B$1, CategoryList, 0)) & “3”)` dynamically creates the cell reference in the Inventory worksheet based on the category name and row 3. It combines the text “Inventory!”, the category name, and “3” to form the cell reference like “Inventory!Massage3”.
4. `IFERROR(…, “”)` wraps the INDIRECT function to handle the case where no items exist within the category. If the INDIRECT function returns an error (e.g., if the category doesn’t exist), it will return a blank cell instead of an error message.

Make sure to adjust the named ranges “Category” and “CategoryList” to match the actual ranges in your Inventory worksheet.