IFS with VLOOKUP not functioning

Ok no, not understanding.  Let's break down the formula because I don't think it is what you want and certainly that there is probably an easier way to do what ever you need.

IF(   OR(IFERROR(IFS(  AND(  O3=VLOOKUP(O3,'Dropdown list'!BT2:BT3,1),
                                              O4=VLOOKUP(O4,'Dropdown list'!BT2:BT3,1)),
                                   "yes",
                                   AND(O3=VLOOKUP(O3,'Dropdown list'!BU2:BU4,1),
                                            O4=VLOOKUP(O4,'Dropdown list'!BU2:BU4,1)),
                                   "yes",
                                   AND(O3=VLOOKUP(O3,'Dropdown list'!BV2:BV4,1),
                                            O4=VLOOKUP(O4,'Dropdown list'!BV2:BV4,1)),
                                   "yes",
                                   AND(O3=VLOOKUP(O3,'Dropdown list'!BW2:BW4,1),
                                            O4=VLOOKUP(O4,'Dropdown list'!BW2:BW4,1)),
                                   "yes",
                                   AND(O3=VLOOKUP(O3,'Dropdown list'!BX2:BX4,1),
                                            O4=VLOOKUP(O4,'Dropdown list'!BX2:BX4,1)),
                                   "yes",
                                   AND(O3=VLOOKUP(O3,'Dropdown list'!BY2:BY4,1),
                                            O4=VLOOKUP(O4,'Dropdown list'!BY2:BY4,1)),
                                   "yes",
                                   AND(O3=VLOOKUP(O3,'Dropdown list'!BZ2:BZ4,1),
                                            O4=VLOOKUP(O4,'Dropdown list'!BZ2:BZ4,1)),
                                   "yes"),
                            "no")="yes",
                IFERROR(IF( AND(O3=VLOOKUP(O3,'Dropdown list'!CA2:CA4,1), 
                                            O4=VLOOKUP(O4,'Dropdown list'!CA2:CA4,1)),
                                    "yes"),
                            "no")="yes"),
          "yes","no")

So this is very confusing and uses the functions in weird ways. 

1) It appears both the IFS on line 1 and the IF on line 23 do NOT include a FALSE condition but instead have IFERROR provide the "no" in those cases.  

2) you use the IFS statement for a whole series of possibilities just to output "yes" in each case but then have an OR with 1 more possibility (the IF on line 23) for one more "yes"

3) and after all that work to get either a "yes" or a "no" you then compare with "yes" to output "yes" or "no"

4) in each and every condition you are comoparing O3 and O4 to a column from row 2 to 4 to see if both are present

my proposed solution does make some assumptions like O3 will be in row 3 and O4 will be in row 4 but if that is not the case what are the possibilities?  From your image it appeared row 2 was a head and really didn't need to be or shouldn't be included.  Do you want a "yes" if rows 3 and 4 and O3 and O4 in either order?  The present formula should give a "yes" if it can find O3 and O4 in any one of those columns.  is that what you want or a "yes"/"no" for each column?  

so in your recent comment you circled a single cell with "no" right below 2 cells that have "yellow" in them.  Based back on your first image none of the cells have just "yellow" in them except for the header in column BU, but assuming that header cell with "yellow" is BU2 and O3 and O4 are the 2 cells with "yellow" I actually would expect the result to be "true"