Forum Discussion
If the column contains text/number, then return value
Perhaps you shall use semicolon instead of comma as separator. Please open attached file, formula automatically will be translated into your locale, and it shall work.
Can you please create a sample document where this works? I don't know what I'm doing wrong or different than in Libreoffice, but there I normally put the formula into the required cell and after pressing Enter it was used. In Excel, it either just displays the whole formula in the cell (in bold) or pops up that it's wrong. I use Office 2016 in Slovak.
Sheet1
Porsche Panamera 2019 5656464 Porsche Macan 2016 78797546 Mercedes-Benz GLE 2020 2112456979 Ford Mustang 2016 12578766 Porsche Panamera 2015 2516979 Porsche Macan 2014 1245B78C8 Mercedes-Benz GLE 2021 HGSD646 Ford Mustang 2010 6497989 Mercedes-Benz GLC 2018 57878766 Sheet2
Porsche Macan 2016 78797546 Ford Mustang 2016 12578766 Porsche Panamera 2015 2516979 Mercedes-Benz GLE 2021 HGSD646 Please be sure numbers on both sheets are texts (select them, Ctrl+1, Number -> Text)
Formula will could be as here
I set the format of the whole column as text, I inserted the same formula as you have in the screenshot, I pressed enter and the same error popped up (it's in my language, but I found it in English). I am also sending an excel document.
