Help adding ISBLANK to LOOKUP formula

[NonEventHorizon]

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

[Richard Buttrey]

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

[NonEventHorizon]

Hi Richard,

Thanks for your reply. Please see attached sheet.

Column A has the raw data entered into it. The current formula in Column D then extracts the unique entries from Column A.

[The_Snook]

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

[NonEventHorizon]

Hi Snook,

Thanks for your reply. That works brilliantly, removes the #N/A and appeases my OCD

Weirdly though it then affects my formula in Column F. I'm not sure if you've seen the example sheet I have uploaded, I tested your solution in Cell D53 and it then affects the formula in F53 =COUNTIF($A$4:$A$304,D53) it should return 0 in F53 normally but it returns the result 301 which in turn gives a false result in E1 which sums the total number in Column F.

Do you think I should be nesting an ISBLANK into the formula in F53?

Thanks for your help.

[The_Snook]

The 301 will be caused by the COUNTIF function adding all the blank cells (assuming that your Col A contains no data). I get 274 because your example contains data.

Cell E1 is calculated using column B so may not always reconcile to column E which incorporates customer names into the calculation.

[NonEventHorizon]

Snook, you are a star. The formulas for columns E and F resolve that weird "301" (which incidentally doesn't appear on the actual working sheet I have with data entered... weird!!)

I am completely self taught through forums and youtube when it comes to excel and you have expanded my knowledge, I thank you for that.

[The_Snook]

You're welcome, more than happy to help.

Snook