Forum Discussion
Using IF to return table data
I'm working with a workbook that contains multiple sheets. Each sheet has a table with two columns, "Task" and "Cat", and each table is named (TASK12, TASK19, TASK116, etc.). Both columns contain text.
On a summary tab, I've used the IF function to return the text in the TASK based on the category (CAT) selected. If the category is not Daily Tasks, it returns "".
=IF(TASK12[CAT]="Daily Tasks",TASK12[TASK],"")
This is mostly working, but for some reason I keep seeing #VALUE! errors in some of the results. Sometimes, even within the same formula array:
I've tried:
- Using the CLEAN function to replace text in the fields corresponding to the #VALUE! error
- Removing all punctuation from the corresponding field (even though others have it and those are working)
- Using the IFERROR function
- Using the ISTEXT function
Any ideas what would be causing these random errors?
1 Reply
If i correctly understand what you want to do you can use VLOOKUP or INDEX and MATCH for this. If you work with Office365 or Excel 2021 you can apply XLOOKUP as well.
Look up values with VLOOKUP, INDEX, or MATCH - Microsoft Support
