Forum Discussion
IFS with VLOOKUP not functioning
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")
If the 1st value is in column BT and the 2nd value is in column BT, then return a "yes" if not a "no." This is true for all the following columns. If the 1st value is in column BU and the 2nd value is in column BU, then return a "yes" if not a "no." I am getting no for all arguments except for the 1st column. Any thoughts?
3 Replies
First thought is that formula is crazy.
Second thought is I don't see how you are getting multiply outputs and if you copy-paste / fill to the right then the O3 and O4 will also shift but I don't know what is in P3,P4 and such.
Can you explain a little better what the formula needs to do. From your description I would say the following should work:
=IF((O3='Dropdown list'!BT3:CA3)*(O4='Dropdown list'!BT4:CA4),"yes","no")
Hi! Thank you replying. The formula is crazy. heh. The formula applies only to two cells, the same two cells. The result will change in those cells. Depending on what the result is in both cells, that is when we get a "yes" or "no". See below.
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"
