The goal is to return the first non-blank value in each row from columns B:E, moving left to right. One way to solve this problem is with a series of nested IF statements. Since all cells are contiguous (connected) another way to get the first value is with the XLOOKUP function. Both approaches are explained below.
Nested IF solution
In the worksheet shown, the formula in cell G5 is:
=B5<>"" // B5 is not empty
The overall structure of this formula is what is called a "nested IF formula". Each IF statement checks a cell to see if a particular cell is not empty. If a cell is not empty, the IF returns the value from that cell. If the cell is empty, the IF statement hands off processing to the following IF function. The flow of a nested IF is somewhat easier to understand if we add line breaks to the formula to separate each IF function like this:
= IF(B5<>"",B5, IF(C5<>"",C5, IF(D5<>"",D5, IF(E5<>"",E5, "no value"))))
It is also possible to use the ISBLANK function, which returns TRUE when a cell is blank:
=ISBLANK(A1) // A1 is blank
The behavior can be "reversed" by nesting the ISBLANK function inside the NOT function:
=NOT(ISBLANK(A1)) // A1 is not blank
The original formula above can be re-written to use ISBLANK as follows:
Another way to solve this problem is with the XLOOKUP formula like this:
We can use XLOOKUP in this case because the four cells in B5:E5 are together, so can be provided as a single range. The ISNUMBER function checks the range and returns an array of TRUE and FALSE values like this:
XLOOKUP matches the first TRUE in the array and returns the corresponding value in B5:E5. Note that this approach only works because all the cells being checked are in a contiguous range. You can read more about XLOOKUP here.