Formular changed in MS 365 and lead to error

[Britt26]

Hi,

I have been using this formula for years now and with Microsoft 365 I get an error message and the formula did not work as requested.

This is the formula. 

The goal is to get a value if availabe or a blank field if not available. But now I get a "False" if the value cannot be found in the matrix. 

Do I have to change the brackets in the formular? As they now turn red.

Or do I have to switch from SVERWEIS (Vlookup) to XVERWEIS (Xlookup) ?

Hope someone can help me.

I am looking forward to get some support. Thanks in advance. 

Best Regards

Britta 

[#2]

@Britt26 

The formula looks OK. A slightly shorter version:

=WENNFEHLER(SVERWEIS(A8;'offene Re. Sekr. 05.05.2023'!A:S;19;0);"")

or

=XVERWEIS(A8;'offene Re. Sekr. 05.05.2023'!A:A;'offene Re. Sekr. 05.05.2023'!S:S;"")

If this returns FALSCH, the value of A8 must have been found, but the corresponding value in column S was FALSCH...

[#3]

@HansVogelaar Thanks a lot. I will try this. Best Regards Britta

[#4]

@HansVogelaar 

The XVERWEIS formula works. If A8 is in the other sheet I get the value of column S back. But if A8 is not in the sheet I get a 0. I would like to see either a value or a blank field instead of 0.

Is there a way to adapt the formula?

Best Regards

Britta

[#5]

@Britt26 

If the value of A8 is not present in column A of the other sheet, the formula will return a blank.

If the value of A8 is present in column A of the other sheet, and if the corresponding cell in column S is empty, the formula will return 0.

There are several ways to hide the 0:

• Apply a custom number format to the cell(s) with the formula such as Allgemein;Allgemein;
• Set Excel to hide zero values - see Anzeigen oder Ausblenden von Nullwerten 
• Change the formula to

=WENN(XVERWEIS(A8;'offene Re. Sekr. 05.05.2023'!A:A;'offene Re. Sekr. 05.05.2023'!S:S;"")="";"";XVERWEIS(A8;'offene Re. Sekr. 05.05.2023'!A:A;'offene Re. Sekr. 05.05.2023'!S:S;""))