Forum Discussion

andreacamp63's avatar
andreacamp63
Copper Contributor
Sep 20, 2018

Excel

Good Afternoon!  I have a report that I pull from our payroll processors that is used to show the Cost Numbers assigned to each employee along with the percentages.  We recently switched from using Department numbers to Cost numbers.  We found that using the Cost numbers they can't show the descriptions of the numbers even though they are in the validation tables.  Is there a way to connect the descriptions from the tables into the spreadsheet without the descriptions.  I have attached a sample to show the report I use and the validation table export.  It is only a portion of the report and tables however.

Thank you, Andrea

excel

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 20, 2018

    Hi Andrea,

     

    You may pick-up descriptions with formula like

    =IFNA(INDEX(<validation description range>,MATCH(<CN>,<validation CN range>,0)),"")

    concrete formula depends on how your actual data is structured

    • andreacamp63's avatar
      andreacamp63
      Copper Contributor
      Sep 21, 2018

      Sergei,

      If I have the cost number report without the descriptions in the 1st sheet and then the cost numbers with the descriptions in a 2nd sheet how would I use that formula.  I am not a pro with regards to Excel.  I have attached the report for you to see.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 21, 2018

        Hi Andrea,

         

        With your data the formula will be

        =IFNA(INDEX(Description!B:B, MATCH($D2,Description!A:A,0)),"")

        MATCH finds the position (row number) of the Cost Number cell in Description sheet with the value which is equal to the value of cell D2

        INDEX takes that position and returns the Description from the same row (next cell)

        IFNA returns empty string of Cost Number is not found in Description sheet

         

        Eneter the formula in E2 and drag it down till end of you range (or select entire range includes E2 and press Ctrl+D).

         

        Just in case, more suitable if you work with Excel Tables. Using them you don't depend on the size of your range and formulas will be automatically populated when you add/change them. I added two more worksheets with the tables to illustrate. The formula will be

        =IFNA(INDEX(Description[Description], MATCH([@[Cost Number]],Description[Cost Number],0)),"")

        Please see attached.

Resources