Forum Discussion

xpres91's avatar
xpres91
Copper Contributor
Jan 07, 2022

Index Match Return with IF function.

can somebody help me with this, I've been struggling to mix IF, INDEX and MATCH functions.   My target usage of the formula is to get the value of another index if the first condition of the index ...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Jan 07, 2022

xpres91 

Use "", not " " (the latter is a string consisting of a space).

  • xpres91's avatar
    xpres91
    Copper Contributor
    Jan 08, 2022

    HansVogelaar, I Already tried that before, I just put a space to make it visible 🙂 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jan 08, 2022

      xpres91 

      Could you attach a small sample workbook demonstrating the problem?

      • xpres91's avatar
        xpres91
        Copper Contributor
        Jan 08, 2022

        HansVogelaar I'm wondering if the "IF" function is not compatible with the INDEX and MATCH function. 

         

        I even tried to test the formula as:

        =IF(INDEX(DATABASE.!$I$2:$I$1200,MATCH(1,($B$1=DATABASE.!$A$2:$A$1200)*(C17=DATABASE.!D2:D1200),0))="","not found","found" 

         

        Instead of giving me "not found", it gives me "#N/A".

         

        This is the breakdown of my formula

        DATABASE.!$I$2:$I$1200 - Rank of the crew w/c the formula will look into.

        $B$1 - Manager who is In-charge of the crew.

        C17=DATABASE.!D2:D1200 - Date of the Registration

         

        My Goal: To Show the Rank of the Crew if I have selected a manager w/c the crew is registered to, in respect to the registered date.

         

        I hope you or somebody can help me 🙂

         

        Hope the attachment will help. 

         

        Sheet, string connected toSheet w/c I want the result to be shown

Resources