Forum Discussion
If the column contains text/number, then return value
Excel 2016: In Sheet1 I have a complete list of products in which I need to somehow mark the products that are also in Sheet2, preferably so that the value is displayed in the next blank column, based on a comparison of product numbers from the following columns:
- in Sheet1, these product numbers are in column C
- in Sheet2 in column B
- in both cases, it starts from the second line.
In LibreOffice Calc, I used the formula:
=IF(ISNA(VLOOKUP(C2,Sheet2.B:B,1, 0)),"",C2)
I inserted it in Sheet1 into an empty cell in the row of the first product and then dragged it to the last cell, then either the product number or the empty cell was displayed instead of the formula.
I found many formulas on the Internet, but none of them worked for me and the program wrote that there was an error in the formula. Later I would also need to compare product names. Thanks for help.
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.
7 Replies
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
