Search code examples
excelfunctionexcel-formulalogicformula

Logical test in Excel based on the Value in the Two cell

I need Excel Expertise support to solve my Excel logic test issue.

I have code in two tables which update the value as 1 and 0 based on the availability, SO in a another table I am updating combination of two letters.

Considering AND Logic need to give True or False in the next table

  • 0 0, 0 1 and 1 0 - False
  • 1 1 in Both Code - True

Table Detail

Please get the Below LOOM link to Video Explanation.

https://www.loom.com/share/6903e230f9914940943f61b8f823e066

Overall information

enter image description here

Solution

  • A Multi-Lookup

    enter image description here

    =LET(lData,P5:T9,mh,B3:D3,md,B5:D9,ah,F3:M3,ad,F5:M9,
        y,TRUE,n,FALSE,e,"",
    fr,@ROW(lData)-1,
MAP(lData,LAMBDA(r,LET(
        mm,INDEX(md,@ROW(r)-fr,XMATCH(LEFT(r,1),mh)),
        am,INDEX(ad,@ROW(r)-fr,XMATCH(RIGHT(r,1),ah)),
    IFERROR(IF(mm+am=2,y,n),e)))))