Putting a value into a cell based on values in other cells

Hello!

I will try to explain in words what I am trying to achieve, but I am not sure I will be able to explain that, so I am attaching a file as well (it has macros, but you can disable them - they are not relevant for this task).

There are two sheets in a workbook.

In sheet_1 in C6 I have a value, which I choose from the drop down list, set up from values in C column in sheet_2.

I would like to write a function for each cell in column D in sheet_1, which would:

look at the chosen value, for example, in C6 (or any other cell in column C) in sheet_1,
and, as this value is from C19 in sheet_2, look at the value in the same row (row 19) but in column D (namely cell D19),
and if the value in D19 = "done", then the value in D6 in sheet_1 should become "closed".

I tried to write a function, but I don't see how to move forward and improve it in such a way that it finds the correct row in column D in sheet_2, which corresponds to the value in a specific cell in column C in sheet_2, and thus in column C in sheet_1.

Please, see a file for clarification.

Thank you very much!

Originally Posted by ChemistB

Try this

=IFERROR(IF(VLOOKUP(C6,sheet_2!$C$16:$D$29,2,FALSE)="done", "Closed",""),"")

Thank you so much! It did the trick. I will now add OFFSET to make sure that if rows are added, the value returned in still a correct one (so to avoid hardcoding 2).