Forum Discussion
Formular changed in MS 365 and lead to error
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
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...
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
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;""))
HansVogelaar Thanks a lot. I will try this. Best Regards Britta
