Finding a number or text in an unsorted list and showing it as "Done" next to another list

[Ginge1267]

Hello and thanks for looking at my problem.

For some reason I cannot attach a file so I'll try to write it out and hope it makes sence.

Ok so here is the unsorted list (in a column) these are entered in the order they are done. Each number will only appear once as they are selected from a drop down list.
.......A
1 170115
2 170116
3 170117
4 170107
5 170108
6 170103
7 170104
8 170105
9 170101
10 170110
11 170111
12 170112
13 170113
14 170102
15
16
17
19
20


In another column list, lists all the number that need to be done. I would like the word Done in green or green background to appear in the adjacent cell when it is found in the first list and not until.
........G.......H
1 170101 DONE
2 170102
3 170103
4 170104
5 170105
6 170106
7 170107
8 170108
9 170109
10 170110
11 170111
12 170112
13 170113
14 170114
15 170115
16 170116
17 170117
18 170118
19 170119
20 170120


The conditional formatting I think I am ok with.
It is the IF statement I am struggling with. I have tried this by naming the first list as a range "list1" and also just by selecting the range A1:A20

These are what I have tried and do not work.

=IF(list1=G1,"Done","") Here I get the blank, the number 170101 is in the list and its refering to cell G1 which is in this case 170101 but its not finding it.
=IF(list1=170101,"Done","") Same as above but I specified the number.
=IF(A1:A20=170101,"Done","") Here I get the blank, the number is in the list but its not finding it and I specified the number 170101 I wanted it to find.
=IF(A1:A20="170101","Done","") Same as above with quotes around the number.

I probably need a different function I've not used before and don't know exists but I cannot seem to find a function that I need.

Please help.

[cytop]

You can use
=IF(COUNTIF(G1, A1:A20)=0)
as a Conditional Format function to colour the number cell directly, or
=IF(COUNTIF(G1, A1:A20)=0,"","Done") in the next column add to the text to the column and CF that depending on the text in the cell.

[Ginge1267]

Dear cytop,

Many thanks, I got there in the end by using in cell H4 =IF(COUNTIF( $A$1:$A$20,G4)=0,"","Done") then conditionally formatting the cell G4 with =H4="Done"

It was the COUNTIF function I hadn't used before so thanks for the heads up with that.

[cytop]

Apologies for transposing the parameters... Typed freehand on a Tablet and no Excel.

[Ginge1267]

Haha, no problem, at least you got me pointed in the right direction.

Do you know of a list of functions that gives better detail of what those functions can do?

[FlameRetired]

Haha, no problem, at least you got me pointed in the right direction.

Do you know of a list of functions that gives better detail of what those functions can do?

Ahhh Ginge1267 ... "IFONLY"! lol I've tried and I can't find one.

The breadth of the answer(s) is enormous just concerning COUNTIF alone.

The best I can offer is what I suppose the bulk of forum contributors do.

• come back to the forum(s) often and read some threads to learn
• come to understand the solutions offered ... there will be many applications of this and other functions you won't find in the help documentation.
• many of those are quite creative.
• attempt a solution yourself ... self education is the best education.
• play with the functions ... mock up problems ... play 'what-if'
• you don't have to post your solutions ... but ... one of the most effective ways to learn and get inspiration on a subject is to teach it.
• We get what we give.
• Start simple though.

I hope this has helped.
Regards,
Dave

[Logit]

https://support.office.com/en-us/art...1-63f26a86c0eb

https://exceljet.net/formulas

https://www.techonthenet.com/excel/formulas/