Help adding ISBLANK to LOOKUP formula

Hi Everyone.

I'm hoping someone can help or point me in the right direction to find the solution. I am using the below formula to extract a list of unique entries to Column D from a range in Column A:

=LOOKUP(2/1(COUNTIF($D$3:$D$3,$A$4:$A$304)=0),$A$4:$A$304)

Sometimes there could be 10 unique entries, sometimes there might be 20 in a dynamic list with space for 30 entries. This leaves cells in my unique entry list that don't get populated with a #N/A..... Unfortunately it is setting my OCD on fire!!

I have tried the following to clean this up and leave the unpopulated cells blank but it gives me a circular cell reference:

=IF(ISBLANK(D3),"",LOOKUP(2/1(COUNTIF($D$3:$D$3,$A$4:$A$304)=0),$A$4:$A$304)) - This was a bit of guessing on my part as to how the formula should look.

I know really I need the formula in another cell and looking more and more at my guess work I'm realising that it needs to perform the LOOKUP logic test first and then leave the cell blank if there are no further unique entries. I thought maybe using IFNA I could achieve this but I'm not sure where to nest this in the formula or the right syntax and structure to do it.

Any help would be greatly appreciated.

Thanks in advance

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Normally I'd use either a filter to extract unique values, or use the Remove Duplicates functionality

Hi NonEventHorizon,

You can wrap the IFERROR function around your formula to remove the error messages.

Please Login or Register  to view this content.

You could also tidy up columns E and F using the IF function as follows:

Col E

Please Login or Register  to view this content.

Col F

Please Login or Register  to view this content.

Hope this helps.

Snook