PC/Windows 10 Enterprise
MS Office Professional Plus 2019
I've been unable to resolve this lookup problem for a few days now. I believe it's a formatting issue, but I welcome any feedback.
I'm building a workbook that uses multiple sheets of raw data imported from external sources, each of which has a corresponding "processing" sheet that organizes and simplifies the data so I can reliably reference it from my main "summary" sheet. I'm down to the last few formulas to get the data I need into the "summary" sheet and I'm running into a reference problem.
My "processing sheets" use INDEX/MATCH and SUMIFS formulas to consolidate data, which I would then like to reference in my "summary" sheet. However, when I create a VLOOKUP or INDEX/MATCH formula (I've been trying both as a way to resolve this issue) within my "summary" sheet, it fails to find the reference within the consolidated array in the corresponding "processing" sheet. If I manually type the reference into the array (in place of the formula that would normally display that reference), the VLOOKUP or INDEX/MATCH formula in the "summary" sheet works perfectly.
I've played with cell formatting a bit but I can't seem to get the lookup formula to find the reference within the array.
I apologize if this is difficult to follow, my eyes are starting to cross on this one. I'm not entirely sure which formulas to include in this post as nothing makes much sense without context, but at the very least I'll include the an example of the formula I used to create the "consolidated" (unique) array that the lookup formula is failing to search:
=IFERROR(INDEX($A$4:$A$1003,MATCH(0,INDEX(COUNTIF($F$3:F3,$A$4:$A$1003),),0)),"")
This is the upper-most cell in column F; this formula advances down the column to essentially perform a UNIQUE function. In this case, Column A contains the raw data with duplicates and Column F is the corresponding list of unique values. To make matters a bit more difficult, the data I'm trying to reference is both text and numbers; in some cases it's a number that is actually just a project name, in other cases there are letters or special characters.
I would be happy to provide any additional data if anyone has any thoughts on this. Please excuse any missteps or issues with posting etiquette; this is my first post!
Thank you.
