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
=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