Multiple IF(AND function

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
154
Office Version
  1. 2021
Platform
  1. Windows
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....
Untitled.png


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
 
=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)
 
Upvote 0
=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
 
Upvote 0
=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"))
 
Last edited:
Upvote 0
Solution
=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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top