DashboardNovice
Member
My formula is in column G. Column L shows the result I am trying to get.
In column G, if you remove the UNIQUE ( ) with the FILTER ( ) within it, and replace that with "", you will have just the XLOOKUP. XLOOKUP would return blanks where project ID = 5 digits. I highlighted these cells in yellow for clarity. I need to tell Excel what to do when column G evaluates to blank. This is where the FILTER ( ) comes in.
Here is what I am trying to do:
For each blank in column G (e.g. 32026), look to the project IDs below it that share the same 5 digit project ID (32026.001 and 32026.003) and return the state abbreviations from column G.
If the same state is returned more than once (e.g. G10:G11), use the UNIQUE ( ) so that only one value is returned, e.g. L9.
If different states are returned (e.g. G13:14), return the word "Multiple," e.g. L12.
The problem is that FILTER is returning #VALUE! inside of UNIQUE( ). And because of that, I can't get as far as using error handling to return "Multiple."
Maybe FILTER isn't the best function to use for this?
In column G, if you remove the UNIQUE ( ) with the FILTER ( ) within it, and replace that with "", you will have just the XLOOKUP. XLOOKUP would return blanks where project ID = 5 digits. I highlighted these cells in yellow for clarity. I need to tell Excel what to do when column G evaluates to blank. This is where the FILTER ( ) comes in.
Here is what I am trying to do:
For each blank in column G (e.g. 32026), look to the project IDs below it that share the same 5 digit project ID (32026.001 and 32026.003) and return the state abbreviations from column G.
If the same state is returned more than once (e.g. G10:G11), use the UNIQUE ( ) so that only one value is returned, e.g. L9.
If different states are returned (e.g. G13:14), return the word "Multiple," e.g. L12.
The problem is that FILTER is returning #VALUE! inside of UNIQUE( ). And because of that, I can't get as far as using error handling to return "Multiple."
Maybe FILTER isn't the best function to use for this?
