Hello everyone, I just recently moved from Google Sheets to Excel. Already facing some challenges. Hope someone can help me with it.
So, in Google sheets, in case you want your formula to expand downwards in the column, you can use an array formula with a formula in it (not all formulas can be used but still), Check the example below.
I have two columns: Name and Surname. I need to join them and separate names and surnames with a space. In Google sheets I would use the next formula in the header:
=arrayformula({"Name and Surname;if(len(A2:A),A2:A&" "&B2:B,)"})
That actually states that I am using an array, where the first line is the header "Name and Surname", then the formula checks the A column row by row. If there is a value in a certain row in column A, it will join the value of this row in column A with the value of this row in column B value and separates them by a space. If there is no value in this row in column A, it will leave the cell in this row in column C blank and keep looking for the rows with the value.
Is there something like this in Excel, I am trying to find the answer on my own, but have some problems with it. Thank you in advance!
=MAP(A2:A8,B2:B8,LAMBDA(name,surname,IF(name="","",TEXTJOIN(" ",,name&" "&surname))))You can try this formula which returns the intended result in my sheet.
