Check if value in column A is in column B then check if Column C is in column D

[dinoscool3]

I have two tables, one of registrations, one of watch data. I want to add a column to the registration that shows if they watched, however, the watch data table shows any time they watched, the data is not distinct, and I can't create a relationship between the two tables.

I'm not sure where I found this, but a month ago I came up with this

Watched = IF(registrations'[email] in VALUE('watch_events'[email]),IF('registrations'[webinar_id] in VALUE('watch_events'[webinar_id]),1,0),0)

but it seems to overcount. Everything I find to try to fix this only talks about comparing one column with another, and not bringing up the third and fourth.

How can I create a watch column in the registration table that checks if they watched a particular webinar?

[v-henryk-mstf]

Hi @dinoscool3 ,

To create a watch column in the registration table that checks if they watched a particular webinar, you can use the COUNTIFS function in Excel. This function allows you to count the number of times that a combination of values occurs across multiple columns in your data.

Here's an example of how you can use COUNTIFS to create the watch column:

1 First, create a new column in the registration table called watch.

2 In the first cell of the watch column, enter the following formula:

COUNTIFS(watch_events[email], registrations[email], watch_events[webinar_id], registrations[webinar_id])

This formula will count the number of times that the email and webinar_id combination from the current row of the registration table occurs in the watch_events table.

3 Copy the formula down to the other cells in the watch column to calculate the watch status for each registration.

4 Finally, you can use an IF formula to compare the value in the watch column to 1, and return Yes if the value is 1 and No if the value is 0. For example, in the first cell of the watch column, you can use the following formula:

result=IF(watch=1,"Yes","No")

I hope this helps! Let me know if you have any other questions.

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dinoscool3]

But I'm trying to do this in Power BI, not excel?