Formula to cross reference data and compile into one sheet

Formula to cross reference data and compile into one sheet

Hello,

I am needing to take data from one sheet and create a simplified version of it in another sheet. I have tried using MATCH & INDEX and VLookup formulas but none have seemed to work. Details on what I am specifically trying to do are below.


Sheet number 1 has raw data in it extracted from a survey, this raw data is connected to a specific user ID wihtin the same row. Sheet number 2 will take this raw data and reference certain cells all while using the specific user ID.


For example: sheet number one says customer003 has passed the evaluation. Sheet number 2 shows a row for customer003 and under column "evaluation" it reference sheet 1 to show the evaluation has been completed.


I was utilizing MATCH and INDEX since VLookup only references the first column but MATCH and INDEX do not seem to be working when I attempt to do this.


Does anyone know why? Thanks!

Answers

  • Hi @Learning Team Group Alias

    An INDEX(MATCH formula sounds like it would be best for this, you're right! When you say that it's "not working", could you be more specific?


    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}), 1)

    Or in your case:

    =INDEX({Column with Complete}, MATCH([User ID]@row, {User ID in the other sheet}), 1)


    If you have multiple criteria to look for (for example the User ID and other elements), then you may want to use an INDEX(COLLECT. Paul has a great example of this in his response on this post, here.


    If this isn't working for you, it would be helpful to see a screen capture of both your source sheet and destination sheet, along with any errors you are seeing and a copy/paste of the formula you're using... but please block out any sensitive data!

    Cheers,

    Genevieve

  • Compare Two Columns and Highlight Matches

    Select the entire data set.

    Click the Home tab.

    In the Styles group, click on the 'Conditional Formatting' option.

    Hover the cursor on the Highlight Cell Rules option.

    Click on Duplicate Values.

    In the Duplicate Values dialog box, make sure 'Duplicate' is selected.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @lewis hamilton

    Your solution is for Excel and not Smartsheet so that it won't work, unfortunately.

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.