I need to form a formula with 2 condition

[maniacs205]

I have Sheet A
Colomn A : Name -> example Catherine -> uncompleted set
Colomn B : is just either a 'girl' or 'boy'

I have sheet B
Colomn A : Name -> example Catherine -> complete set
Colomn B : is just either a 'checked' or 'unchecked'

My question is
I need colomn B in sheet B to form a formula calling from sheet A (colomn A & B) with using if and countif to check :

1. If all name in Sheet B (colomn A) has been written/inserted in Sheet A (colomn A) -> if it has been written, then it shall be returned as "checked", otherwise "unchecked"
2. If it's a boy, then I want it to return as "checked" but if it's a girl, i want it returned as "unchecked"



I tried this but didn't worked=IFS(COUNTIF('Sheet A'!A:A,B3)>0,"Checked","Unchecked"), 'Sheet A'!B:B, "Unchecked")

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[AliGW]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[maniacs205]

this is too much request and I won't give my work since it's confidential, I usualy were never asked such request by any moderators.

I cant even attached a file here

[AliGW]

Yes, you can attach a workbook here, and no, I am not asking to see sensitive data. You can desensitise it first. Providing a workbook makes it much easier for us to help you. It's up to you.

Is this a GoogleSheets query? If so, I'll move it to the correct section for you.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Sam Capricci]

Thought I'd give it a shot based only on your first post and my interpretation. So attaching what I think you are asking for.
here is the formula used in sheetB =IF(VLOOKUP(A1,SheetA!$A$1:$B$14,2)="boy","checked",IF(VLOOKUP(A1,SheetA!$A$1:$B$14,2)="girl","unchecked",""))
I used the range of A1 to B14 but you can adjust the range based on your needs AND if your settings require you to, you might have to change the commas (,) to semicolons (.

[maniacs205]

sorry, i must say it's not worked perfectly.

Because we found one name could actually be either a boy or girl

[maniacs205]

and if in sheet B, there's a name written but not written in Sheet A, then it should be stated as unchecked

[Sam Capricci]

sorry, i must say it's not worked perfectly.
Because we found one name could actually be either a boy or girl

well, I'm not sure that any formula can account for trying to discern the name difference for a boy or girl when the name could go with either.

And as for your second issue, I just gave you a very limited "interpretation" of what you were saying in post #1 and trying to provide you with a concept of how it might work.
If you want a better answer you should do what Ali requested in posts #3 and 5 about creating and uploading a workbook (desensitized) that shows better examples of what you are dealing with.

[maniacs205]

hi

you already created the workbook for us nicely

find the name tom in sheet B, he is not to be found in sheet A

[Sam Capricci]

yes, I wanted to show you how it would work if the name wasn't in sheetA but was in sheetB. Instead of erroring it would return a blank.
but rereading your post #8 maybe you want this instead... =IFERROR(IF(VLOOKUP(A1,SheetA!$A$1:$B$14,2)="boy","checked","unchecked"),"")

[maniacs205]

"well, I'm not sure that any formula can account for trying to discern the name difference for a boy or girl when the name could go with either."

The only way to do this, is to make it as follow : if there's a name "jeje" as boy or a girl, then it shall return as "checked" either way

[Sam Capricci]

If you are going to have multiples of a name which could go either way a vlookup will not be of any help. A vlookup will only bring back the first instance of what it finds.
First names and last names would be more helpful. Without a sample workbook it is just guessing. I get that you don't want to share a confidential workbook but without more information we continue to guess. You've ignored repeated requests for a sample workbook. So I'm not going to continue to work up samples that are guesses. Good luck, maybe someone else will jump in with their guesses.