Forum Discussion
HELP with IF Formula
I am trying to create a formula that will look at four cells and depending on the condition, return a value from another four cells. If two or more cells are "Yes", I need to return "ERROR". Can anyone point me in the right direction?
You don't provide detailed information, so I'll just guess.
Let's say the first four cells are B2:B5, and the second four cells are D2:D5.
=IF(COUNTIF(B2:B5, "Yes")>1, "ERROR", IFERROR(INDEX(D2:D5, MATCH("Yes", B2:B5, 0)), ""))
If you have Microsoft 365 or Office 2021:
=IF(COUNTIF(B2:B5, "Yes")>1, "ERROR", XLOOKUP("Yes", B2:B5, D2:D5, ""))
- Sorry for the lack of information. Here is what we are trying to do: Cell B7 is a dropdown listing of states. Selecting the state (i.e. FL) will populate cells B18 to 22 with data. Cells B9 to 12 have YES or NO options. Only ONE of those cells can be YES. If two or more are YES then we need to return an ERROR to cell b14. Depending on the selection, Cell B14 will pull the required information from one of the cells between B18 and B22. I have created the IF function that will do that, but do not know how create it with the ERROR message. I will try what you have given me and see what happens.
B9:B12 contains 4 cells, but B18:B22 contains 5 cells, so there's a problem...
