I need help creating nested formulas to pull data from multiple sheets.

orenwick

Hi Smartsheet Community,

I am working on creating a training matrix based our training documentation.

Here is the set up of the training matrix: peoples names as columns and the trainable documents in the rows. I added the helper row because I couldn't figure out how to use the columns in a formula.

This is the separate sheet that records our training attendance.

What I would like for it to do would be:

If Employee Name (from training attendance sheet) matched Employee Name Column (on matrix sheet identified as helper row) AND Training Attended (from training attendance sheet) matches Training Title (on matrix sheet) THEN return "Level of Training" (On matrix sheet).

The other part of this is that I have different levels of training so I would like it to return the highest level of training in case multiple trainings are done.

Hierarchy lowest to highest: Overview, Hands On, Fully Trained, and Trainer.

Thank you,

Olive

orenwick

Here is what I have attempted but it isn't really working. I'm currently getting an #invalid operation error but it was sort of working earlier. The change I made was updating {Employee Name} column in the reference which I'm not sure why that broke it.

=IF(OR($[Olive Renwick]$1 = {Employee Name}), VLOOKUP([Training Title]@row, {Lookup Table}, 2, false), "Training Needed")

Also having the issue that instead of returning Training Needed when it doesn't find a match it will say #No Match but only sometimes.

Genevieve P.

Hi @orenwick

For this type of formula you'd want to use an INDEX(MATCH(MATCH instead of a VLOOKUp.

This is the structure:

=INDEX({Entire Range}, MATCH([Row Match]@row, {Column with Row Match Value}, 0), MATCH([Column Match]@row, {Top Helper Row}, 0))

So in your case, something like this:

=INDEX({Entire Range from Training to Thom}, MATCH([Training Attended]@row, {Training Title Column}, 0), MATCH([Employee Name]@row, {Top Helper Row}, 0))

Note that the [Training Attended] will need to only have a single value for it to find the matching Training Title in the other sheet - it won't be able to parse out multi-select items and match them individually.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

orenwick

Hi Genevieve,

Thank you for your response. Sorry mine is so delayed!

I am unfortunately still struggling with this. Here is what I translated from what you sent for the sheet references:

=INDEX({Level of Training}, MATCH({Training Attended}, $[Training Title@row, 0), MATCH({Employee Name}, [Helper Row]$1, 0)

The error I am receiving is #Incorrect Argument Set.

Here is my breakdown of the equation and what I was expecting it to do:

I am writing the equation in the matrix sheet so the attendance references will be the { } ones.

Index: {Level of Training} is what I want returned from the attendance sheet.

In your response you have a whole set of columns highlighted which in this case would be in the attendance sheet Employee Name -> Level of Training. I don't think I am understanding how it knows to return only level of training. When I set the index range as Employee Name -> Level of Training I still get the #Incorrect Argument Set error.

Match 1: {Training Attended} (from attendance sheet) is getting matched to $[Training Title]@row (from matrix sheet).

Match 2: {Employee Name} (from attendance sheet) is getting matched to a single name in the [Helper Row]$1 (from matrix sheet).

Thank you again for your help! Hopefully this clarifies better what I am trying to do and also what I am doing wrong.

Best,

Olive

Paul Newcome

@orenwick Your MATCH function syntax is off.

You have

MATCH({Range}, "search value"

but it should be

MATCH("search value", {Range}

orenwick

@Genevieve P. @Paul Newcome

That fixed it! Thank you both so much!

Paul Newcome

Happy to help. 👍️

To be completely honest... I still make that mistake about half the time I am using the MATCH function. Hahaha.

Genevieve P.

Thanks for stepping in, Paul! 🙂

Paul Newcome