News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Excel-IF error & Index / Match

    Viewing 4 reply threads
    • Author
      Posts
      • #2267055 Reply
        WSMNN
        AskWoody Plus

        I tried several attempts to this formula. It combines the IFERROR and the INDEX/MATCH formulas. I want to choose the result for the INDEX/MATCH however if that results in an error, I want a second formula to look to another column for the result. however, I only get the primary results NO MATTER what I try to do.

        =IFERROR(INDEX(‘COST ACTG DEPT # & TITLE’!$E:$E,MATCH(HAMILTON!Y4,’COST ACTG DEPT # & TITLE’!$C:$C,0)),INDEX(‘COST ACTG DEPT # & TITLE’!$D:$D,MATCH(HAMILTON!Z4,’COST ACTG DEPT # & TITLE’!$C:$C,0)))

        FORMULA BROKEN DOWN BY PRIMARY AND ERROR CAPTURE:

        1.> =IFERROR
        2.> (INDEX(‘COST ACTG DEPT # & TITLE’!$E:$E,MATCH(HAMILTON!Y4,’COST ACTG DEPT # & TITLE’!$C:$C,0)), *<COLUMN E FOR RESULT PRIMARY>*
        3.> INDEX(‘COST ACTG DEPT # & TITLE’!$D:$D,MATCH(HAMILTON!Z4,’COST ACTG DEPT # & TITLE’!$C:$C,0))) *<COLUMN D FOR RESULT IF ERROR CAPTURED>*

        ANY IDEAS?

      • #2267135 Reply
        Paul T
        AskWoody MVP

        Can you post a sample file for us to test – easier than trying to replicate your work and getting it right/wrong. XLS or ZIP should be OK.

        cheers, Paul

      • #2267152 Reply
        PaulK
        AskWoody Lounger

        Is this feasible?

        On a ‘Testing COPY’ of the sheets, insert some temporary/debugging column(s). Break down the forumlae to insert a T/F [0/1, Y/N] (or other intermediate) value as the result of each IF. Keep recursively reducing a formula until the root problem is isolated. Then rebuild the  complex formula structure, but using these temporary columns data.

      • #2267195 Reply
        RetiredGeek
        AskWoody MVP

        Have you tried the Evaluate Formula on the Formulas Tab to step through the formula and see where it chokes?

        HTH 😎

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

      • #2267304 Reply
        WSMNN
        AskWoody Plus

        Thank you for the three suggestions.  I am attaching a sample of what the formula should do

        The formula is in the RESULT SCHEDULE in the Category column.  the Formula should look to the DATA SCHEDULE  COLUMN C for the initial answer, if there is an error then look to COLUMN B for the answer to be place in the RESULT SCHEDULE for the respective code.

        Hopefully this now makes a little more sense.  It may  not be an IFERROR FORMULA  to make this formula work.

        Thank you.

         

         

        • #2267305 Reply
          PKCano
          Da Boss

          Under the entry box on the left, click “Select file”
          It will open Explorer on your computer.
          Highlight the file and click “Open”

    Viewing 4 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Excel-IF error & Index / Match

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.