Forum Discussion

Oskar_P_J's avatar
Oskar_P_J
Copper Contributor
Mar 29, 2022
Solved

Excel Wenn(Und -> Status aktualisieren

Hallo liebe Community,   Ich wollte mir die Arbeit mit Excel etwas erleichtern, aufgrund meiner fehlenden Kenntnisse bin ich jedoch nicht in der Lage es selber zu machen.   Kurz zu dem Ablauf. Es...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Mar 31, 2022

    Oskar_P_J 

    =IF(NOT(ISNA(VLOOKUP(Tabelle1!C2&Tabelle1!D2&"fertig",Tabelle2!A:A&Tabelle2!B:B&Tabelle2!D:D,1,FALSE))),"x","--")
    =IF(NOT(ISNA(MATCH(Tabelle1!C2&Tabelle1!D2&"fertig",Tabelle2!A:A&Tabelle2!B:B&Tabelle2!D:D,0))),"x","--")

    It seems to work with VLOOKUP or MATCH. Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 29, 2022

Oskar_P_J A single cell is never equal to an entire column, so you IF will always return the if_false value of "--"

 

You probably need a formula with the structure demonstrated in the picture below:

 

Note that it will only pick-up the first matching value though. If you are using MS365 or Excel 2021 there may be others ways.

 

Oskar_P_J's avatar
Oskar_P_J
Copper Contributor
Mar 31, 2022

Riny_van_Eekelen Thank you for your help.

Unfortunately, I am a beginner in Excel. How can I apply your formula to my example? I get a bit confused with your example. Do I have to combine the two formulas? or is my formula completely wrong? Is there a way that Excel automatically searches for these 3 criteria in the respective columns and sets an "x" if all three are met?

By the way, I'm using MS365 so maybe there's a simpler solution? 

Thank you 

O.