Jul 15 2020 10:12 AM - edited Jul 15 2020 10:13 AM
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.
Jul 15 2020 10:26 AM
Jul 15 2020 11:05 AM
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 |
Jul 15 2020 02:35 PM
Please be sure numbers on both sheets are texts (select them, Ctrl+1, Number -> Text)
Formula will could be as here
Jul 16 2020 02:58 AM - edited Jul 16 2020 02:59 AM
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.
Jul 16 2020 08:07 AM
SolutionPerhaps 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.
Jul 17 2020 01:26 AM
Thank you very much, it already works!
Jul 17 2020 10:42 AM
@temporalnaut , you are welcome
Jul 16 2020 08:07 AM
SolutionPerhaps 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.