Help with IF Function in Excel

Question

Help with IF Function in Excel

EH_47 20

I need help with an IF function if that is what makes the most sense for the following scenario:

In Sheet 1, I have a list of User IDs
In Sheet 2, I have the same list of User IDs, but they could be listed multiple times in the same column
In Sheet 2, I have a list of different training codes in another column for training that the users have taken

I need a formula that can help me produce the value TRUE or FALSE in a cell on Sheet 1:

If User ID in Sheet 1 matches User ID in Sheet 2 in a specific column range and in the same row where the User ID matches in Sheet 2 there is a specific training code listed in a specific column range in Sheet 2, then produce the value TRUE. If not, then produce the value False.

Barry Schwarz 5,756 Reputation points

2025-02-18T22:52:27.5+00:00

You need to give us an example that what you are describing. If ID John appears in cell A1 of sheet 1 and in cells A3 and A6 of sheet2, what should appear in cells B3 or B6 of sheet2 that would result in a TRUE result for cell B1 in sheet1? Must both cells B3 and B6 have an appropriate value or is it sufficient for either? Do any other cells besides B3 and B6 impact the decision?
Emi Zhang-MSFT 30,196 Reputation points Microsoft External Staff

2025-02-19T01:38:14.53+00:00

Hi,

What's the meaning of "specific column range"? Is there definitely a Training Code for the User ID in Sheet 2? Please be a bit more precise to explain your requirement or you can upload a screenshot so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.
EH_47 20 Reputation points

2025-02-26T23:39:27.05+00:00

Thank you for the feedback. Here is an example:

If a specific User ID (i.e. JDOEJD23) appears in C2 of Sheet 1 and also appears in column A of Sheet 2 (This specific User ID can appear multiple times in column A of Sheet 2), check to see if that specific User ID completed training code (i.e. UID-SWP-826365) in column D of Sheet 2. The reason I say column A in Sheet 2 and column D in Sheet 2 is because this person's information can move to a different row in the column when I run an updated report.

If JDOEJD23 is in C2 of Sheet 1 and JDOEJD23 is listed in column A of Sheet 2 and training code UID-SWP-826365 is listed in column D of Sheet 2 specifically for JDOEJD23 then say TRUE in D2 of Sheet 1. If not, then say False in D2 of Sheet 1.

Example of Sheet 1:

Example of what I want to find in Sheet 2:
Barry Schwarz 5,756 Reputation points

2025-02-27T02:48:47.45+00:00

It is easy enough to generate the IF for the specific example but do you want to update every formula for the next training code? In other words, how does the IF formula distinguish between the training codes in cells D2 and D3 so that one results in TRUE and the other in FALSE?

In addition, are the User IDs in sheet 2 sorted? If not, are each user's records grouped together?
EH_47 20 Reputation points

2025-02-27T15:59:07.1+00:00

Emi,

Thank you! The formula worked. :)

Answer accepted by question author

0 additional answers

Your answer

Barry Schwarz 5,756 Reputation points

2025-02-18T22:52:27.5+00:00

You need to give us an example that what you are describing. If ID John appears in cell A1 of sheet 1 and in cells A3 and A6 of sheet2, what should appear in cells B3 or B6 of sheet2 that would result in a TRUE result for cell B1 in sheet1? Must both cells B3 and B6 have an appropriate value or is it sufficient for either? Do any other cells besides B3 and B6 impact the decision?
Emi Zhang-MSFT 30,196 Reputation points Microsoft External Staff

2025-02-19T01:38:14.53+00:00

Hi,

What's the meaning of "specific column range"? Is there definitely a Training Code for the User ID in Sheet 2? Please be a bit more precise to explain your requirement or you can upload a screenshot so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.
EH_47 20 Reputation points

2025-02-26T23:39:27.05+00:00

Thank you for the feedback. Here is an example:

If a specific User ID (i.e. JDOEJD23) appears in C2 of Sheet 1 and also appears in column A of Sheet 2 (This specific User ID can appear multiple times in column A of Sheet 2), check to see if that specific User ID completed training code (i.e. UID-SWP-826365) in column D of Sheet 2. The reason I say column A in Sheet 2 and column D in Sheet 2 is because this person's information can move to a different row in the column when I run an updated report.

If JDOEJD23 is in C2 of Sheet 1 and JDOEJD23 is listed in column A of Sheet 2 and training code UID-SWP-826365 is listed in column D of Sheet 2 specifically for JDOEJD23 then say TRUE in D2 of Sheet 1. If not, then say False in D2 of Sheet 1.

Example of Sheet 1:

Example of what I want to find in Sheet 2:
Barry Schwarz 5,756 Reputation points

2025-02-27T02:48:47.45+00:00

It is easy enough to generate the IF for the specific example but do you want to update every formula for the next training code? In other words, how does the IF formula distinguish between the training codes in cells D2 and D3 so that one results in TRUE and the other in FALSE?

In addition, are the User IDs in sheet 2 sorted? If not, are each user's records grouped together?
EH_47 20 Reputation points

2025-02-27T15:59:07.1+00:00

Emi,

Thank you! The formula worked. :)

Answer 1

Emi Zhang-MSFT 30,196 Microsoft External Staff

Hi,

Please try these formulas:

UID-SWP-826365 training: =IF(COUNTIFS(Sheet2!A:A, C2, Sheet2!D:D, "UID-SWP-826365") > 0, TRUE, FALSE)

User's image

ABC-SWP-123456 training: =IF(COUNTIFS(Sheet2!A:A, C2, Sheet2!D:D, "ABC-SWP-123456") > 0, TRUE, FALSE)

User's image

Hope it will help.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

EH_47 20 Reputation points

2025-02-27T15:57:45.13+00:00

Emi,

Thank you so much for your help! It worked. :)
Barry Schwarz 5,756 Reputation points

2025-02-27T17:33:45.37+00:00
Rather than having the values hard coded and therefore manually updated for every change, try

=COUNTIFS(Sheet2!$A:$A, $C2, Sheet2!$D:$D, D$1) > 0

which can then be copied down for as many users as needed and to the right for as many training codes as needed.

Share via

Help with IF Function in Excel

0 additional answers

Your answer