Multiple IF(AND function

[Ramadan]

I'm trying to compare a cell with a column in other sheets using IF(AND function and to shorten my formula I have created ranges with Name Manager for column B and C in each sheet for example (range Disb = Column B:B in sheet "Dis" and range Disc = Column C:C in sheet "Dis"..etc for other sheets and my formula became is like this
=IF(AND(D5=Disb),(E5=disc),District1!$B$2) to check (if D5=any cell in Col B AND E5 = any cell in Col C so retun only cell B2 in the sheet . so far it's ok but I'm facing two probelms


1- when i try to make the formula return ("") if no match like this =IF(AND(D5=Disb,E5=disc,District1!$B$2,"")) excel doesn't accept it and still giving me error and only accept it like this
=IF(AND(D5=Disb),(E5=disc),District1!$B$2)

2- I need also to repeat this formula several time to check other sheets as well like this

=IF(AND(D5=Disb),(E5=disc),District1!$B$2)),IF(AND(D5=carnb),(E5=carnc),'Carnell'!$B$2),IF(AND(D5=Ivrb),(E5=Ivrc),Ivory!$B$2), etc....

but still also getting wrong formula and I don't know what's wrong and also need it to returm empty if no match

Thank you

 

[etaf]

=IF(AND(D5=Disb,E5=disc,District1!$B$2,""))
bracket in wrong place
should be

=IF(AND(D5=Disb,E5=disc),District1!$B$2,"")

also maybe a countif() would help here

AND( countif(range to check, D5)>0 , countif(range to check, E5)>0)

 

[Ramadan]

=IF(AND(D5=Disb,E5=disc,District1!$B$2,""))
bracket in wrong place
should be

=IF(AND(D5=Disb,E5=disc),District1!$B$2,"")

also maybe a countif() would help here

AND( countif(range to check, D5)>0 , countif(range to check, E5)>0)

@etaf thank you for your usual help. I tried your dormula =IF(AND(D5=Disb,E5=disc),District1!$B$2,"") but it return nothing while it should be with data because it's true and I also tried to use COUNTIFS WITH IF like this =IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!B2) and it works but when I try to repeat it to check other sheets like this

=IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!$B$2),IF(COUNTIFS('Carnell'!B:B,D5,'Carnell'!C:C,E5)>0,'Carnell'!$B$2) i get #Value Error

Pease let me explain what I need exactly maybe I'm looking for the wrong formula. As you can see in the above image I have 7 sheets with the same design and table format , in Col B in each sheet is serial and in Col "C" the Unit Number ... So need the formula in this sheet to check in which sheet the data in both (D5 & E5) are found exactly like this with the same Unit Number in "E" and also the same serial number in the table because unit number might be repeated in other sheets but of course not with the same serial number. and if true to give me just the value in cell $B$2 of the sheet that has the match with the data otherwise keep it empty .. what's imoprtant is also that I need to repeat this formula 7 time to check the 7 sheets

I hope that I could expalin my issue clearly

 

[etaf]

=IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!$B$2),IF(COUNTIFS('Carnell'!B:B,D5,'Carnell'!C:C,E5)>0,'Carnell'!$B$2) i get #Value Error
you have a bracket in the wrong place

=IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!$B$2),IF(COUNTIFS('Carnell'!B:B,D5,'Carnell'!C:C,E5)>0,'Carnell'!$B$2) i get #Value Error

remove that and it should work - with the nested IF for each new sheet

maybe a better way, cannot think off the top of my head

i dont use full column reference - just use a range thst will always exceed the range you want

then add a false value if n found in any sheet

=IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!$B$2,IF(COUNTIFS('Carnell'!B:B,D5,'Carnell'!C:C,E5)>0,'Carnell'!$B$2,"NOT FOUND"))

 

[Ramadan]

=IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!$B$2),IF(COUNTIFS('Carnell'!B:B,D5,'Carnell'!C:C,E5)>0,'Carnell'!$B$2) i get #Value Error
you have a bracket in the wrong place

=IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!$B$2),IF(COUNTIFS('Carnell'!B:B,D5,'Carnell'!C:C,E5)>0,'Carnell'!$B$2) i get #Value Error

remove that and it should work - with the nested IF for each new sheet

maybe a better way, cannot think off the top of my head

i dont use full column reference - just use a range thst will always exceed the range you want

then add a false value if n found in any sheet

=IF(COUNTIFS(District1!B:B,D5,District1!C:C,E5)>0,District1!$B$2,IF(COUNTIFS('Carnell'!B:B,D5,'Carnell'!C:C,E5)>0,'Carnell'!$B$2,"NOT FOUND"))

@etaf Thank you so much you saved me it works now great
can you please have a look to my other question filter formula Filter Function to to return Data based on Partial text