[Solved] Problem using IFERROR, INDEX and SMALL commands

[tallnsknny]

Hi, I'm trying to use the IFERROR INDEX and SMALL commands in the attached file to create a list of specific names that meet a certain criteria. The formula works fine in Cell W4 on the Summary tab, but below that it simply returns errors. What is the error in my formula please? My Excel version is Excel 2016 so that's why I have to try and use these formulas instead of something like FILTER.

Mike

 

[shamimarita]

Hello

Thank you for sharing the details of the dataset. Based on this, it appears that the formula you're working with is trying to match a value in the "Region" column ('Detailed List'!$T$4:$T$3004) with a value in V$3 and returns corresponding values from the "Producer" column ('Detailed List'!$S$4:$S$3004).

Correct Formula:
=IFERROR(INDEX('Detailed List'!$S$4:$S$3004, SMALL(IF(TRIM('Detailed List'!$T$4:$T$3004)=V$3, ROW('Detailed List'!$T$4:$T$3004)-ROW('Detailed List'!$T$4)+1), ROW(1:1))), "")

Explanation:

• TRIM Function: TRIM('Detailed List'!$T$4:$T$3004) is used to ensure there are no leading or trailing spaces in the region values.
• Correct Row Calculation: ROW('Detailed List'!$T$4:$T$3004)-ROW('Detailed List'!$T$4)+1 ensures that the row numbers are relative to the range, not the absolute row numbers.
• Use of ROW(1:1): This returns the appropriate "nth smallest" value, and it will increment as you drag the formula down.

 

[tallnsknny]

Shamimarita,

Thank you so much for your solution. It seems to be working fine now.

Regards,
Mike

 

[shamimarita]

Hello Mike,

Thank you for your kind words! I'm so glad to hear that the solution is working well for you. If you have any more questions or need further assistance, don't hesitate to reach out.

Let’s keep helping each other in the forum and make the ExcelDemy community even better together!