List values based on rows

This is one of those were a vlookup won't cut it. My data looks like this

G -- H -- I
16337 --XX--XX
16337-- 238447-- PI009313
16337-- 238836--XX

16337--XX--XX
16337-- 238447-- PI009313
16337-- 238836--XX

41524--XX--XX
41524--XX-- #VALUE!

41524--XX--XX
41524--XX-- #VALUE!

The -- separates columns in the example, and the XX signifies null cells. I need to build a formula to list of any Column G values, preferably unique, where Column I is #VALUE!. I need a formula because the list will be dynamic and may contain 10K + values altogether, but maybe 100 of those will have the error code.

Well, I am not really sure if formula is the best concept. Ahe you tried advanced filter with duplicates removal?

If you want formula try the array one (comitted Ctrl+Shift+Enter - not just enter) in L2:

Please Login or Register  to view this content.

and copy down

If you allow duplicates it will be substantially shorter (again - array formula):

Please Login or Register  to view this content.