HELP with IF Formula

[Zane_Koonce]

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?

[#2]

@Zane_Koonce 

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, ""))

[#3]

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.

[#4]

@Zane_Koonce323 

B9:B12 contains 4 cells, but B18:B22 contains 5 cells, so there's a problem...

[#5]

I figured out the issue and worked around it. I added another row so I have equal number of cells. Cell B9 was asking if there was a curb or guardrail, simple YES or NO. If YES, return cell B19, NO returns cell B20 to B15. I added inserted a row so to ask "With curb?" and then "Without curb?". What you sent me works perfectly now. Thank you.