Formula to cross reference data and compile into one sheet

Learning Team Group Alias · 08/02/20

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!

Genevieve P. · 08/03/20

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

Andrée Starå · 08/03/20

@lewis hamilton

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

Formula to cross reference data and compile into one sheet

Answers

Help Article Resources

Categories