SOLVED

If the column contains text/number, then return value

Copper Contributor

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.

7 Replies

@temporalnaut 

Formula shall work if change a bit as

=IF(ISNA(VLOOKUP(C2,Sheet2!B:B,1, 0)),"",C2)

@Sergei Baklan 

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 20195656464
Porsche Macan 201678797546
Mercedes-Benz GLE 20202112456979
Ford Mustang 201612578766
Porsche Panamera 20152516979
Porsche Macan 20141245B78C8
Mercedes-Benz GLE 2021HGSD646
Ford Mustang 20106497989
Mercedes-Benz GLC 201857878766

 

Sheet2

Porsche Macan 201678797546
Ford Mustang 201612578766
Porsche Panamera 20152516979
Mercedes-Benz GLE 2021HGSD646

@temporalnaut 

Please be sure numbers on both sheets are texts (select them, Ctrl+1, Number -> Text)

Formula will could be as here

image.png

 

 

@Sergei Baklan 

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.

1.pngerror.png

 

 

best response confirmed by temporalnaut (Copper Contributor)
Solution

@temporalnaut 

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.

@Sergei Baklan 

Thank you very much, it already works!

1 best response

Accepted Solutions
best response confirmed by temporalnaut (Copper Contributor)
Solution

@temporalnaut 

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.

View solution in original post