Forum Discussion
How to autofill multiple columns in excel based on more than one sheet?
I have developed this formula in Excel to record
1)whether a user name it taken through binary code of 1/0,
2) The name of the person the username is taken by.
The sheets I am working with look like this:
I
I used this formula to autofill the Status column;
=MIN(1, IFNA(MATCH(A2, Sheet1!B:B,0), 0))
My question is how can i adjust the formula to also be filled based on if the contents appear in sheet 1 OR sheet 2?
I have tried this but it doesn't work:
=MIN(1, IFNA((MATCH(A2, Sheet1!B:B,0),(MATCH(A2, Sheet2!B:B,0)), 0))
My desired outcome looks like this:
3 Replies
B2
=COUNTIFS(Sheet1!$B$2:$B$10,A2)C2
=XLOOKUP(A2,Sheet1!$B$2:$B$10,Sheet1!$A$2:$A$10,XLOOKUP(A2,Sheet1.5!$B$2:$B$10,Sheet1.5!$A$2:$A$10,""))Detlef_Lewin Hello, Thank you for your help. I have some follow up questions. When I've tried this in B2, it doesn't record that this username is in use in sheet 2. Also the second line of code in C2 returns a #NAME?
Corrected formula for B2:
=COUNTIFS(Sheet1!$B$2:$B$10,A2)+COUNTIFS(Sheet1.5!$B$2:$B$10,A2)#NAME? error implies that your are working with an older version of Excel. What version do you use?
