Lookup Visible values on another sheet

[jomili]

I found a formula online that does what I need (pulls in the visible values from another sheet) but there was no explanation of how the formula worked. I was able to modify it to work for what I needed, but I'd like to understand it so I can use it in other applications, so I'm hoping someone will have pity and explain the formula to me. "KeyValues" is a named range, corresponding to column F on the "Enter FTEs" tab. See the attachment to see the formula in operation. The formula in question is reprinted below.

Please Login or Register  to view this content.

Thanks,
John

[Glenn Kennedy]

Are you sure it is doing what you think it is doing? It is pulling up UNIQUE non-blank values from column F, in the opposite order to their occurrence on the source sheet (last one to occur on the source sheet appears first on the destination sheet). It sort-of depends what you mean by "visible"... If you mean that you want it to return ONLY those values that remain visible AFTER a filter is applied to the source sheet... then it does not do what you want.

So... what do/did you want it to do?

[jomili]

Sorry I didn't put that in my first post. Yes, what I wanted was a formula to pull up the unique visible values in that column. This one accomplishes that admirably, I just don't know how it works. Of course, I was savvy enough to wrap the IFERROR around it, and to create my own Named Range, but other than that the formula is as I found it, but with no explanation of how it works.

[jomili]

Found the explanation at https://exceljet.net/formula/extract...ms-from-a-list

Please Login or Register  to view this content.