Matching to another sheet and returning what column it falls underneath

MJones

In my first sheet, I am trying to create a formula under "Risk Factor" that matches whatever falls under the infraction columns; in this case "unsafe handling procedures", and return the category it falls underneath in my second sheet, so in this case "Minor".

1st Sheet:

Reference Sheet:

Paul Newcome

Try adjusting your reference sheet so that all risks are in one column and the corresponding risk factor is in another.

Then you would use something along the lines of

=INDEX({Risk Factor Column}, MATCH([1pt Infractions]@row, {Risk Column}, 0))

MJones

Thank you! I'm currently getting a #NOMATCH, formula below:

=INDEX({Safety Points Risk Levels - Reference Shee Range 4}, MATCH([1pt Infractions]@row, {Safety Points Risk Levels - Reference Shee Range 1}, 0))

Adjusted reference sheet is:

Paul Newcome

Make sure they are an exact match by clicking into both cells as if you are going to edit them. If you have more than one space in between words it will be stored on the back end but will only have one space visible.

For example:

Each of these words have 5 spaces between them

But as soon as I "Post Comment" only one is visible between each word.

MJones

Thank you Paul! It worked (ish). The current working formula is:

=INDEX({Safety Points Risk Levels - Reference Sheet RiskFa}, MATCH([Pedestrian Infractions]@row, {Safety Points Risk Levels - Reference Sheet Infra}, 0))

My only question is, currently the only way to pull the risk level is to specify within the formula the column it's falling under. In this case, the entry in the first row (which was an input from the form) was a "Pedestrian Infraction". Therefore, within the formula I specifically referenced the Pedestrian Infraction column. What I would like the formula to do, is look at the range of columns that are highlighted, and still be able to match it. When I try to use the below as the formula, I get an #Incorrect Argument Set

=INDEX({Safety Points Risk Levels - Reference Sheet RiskFa}, MATCH([MHE Infractions]@row:Conveyor@row, {Safety Points Risk Levels - Reference Sheet Infra}, 0))

Is there any way I could reference the range of columns instead of just one? I hope this makes sense.

Paul Newcome

@MJones Try this:

=INDEX({Safety Points Risk Levels - Reference Sheet RiskFa}, MATCH(JOIN(COLLECT([MHE Infractions]@row:Conveyor@row, [MHE Infractions]@row:Conveyor@row, @cell <> "")), {Safety Points Risk Levels - Reference Sheet Infra}, 0))

MJones

It works! Thank you so much Paul!

MJones

@Paul Newcome : Apologies, another question!

What would be the best way to summarize a few key points in this sheet? Ideally, I would like to be able to go to a summarization of who's names are on the sheet and how many points they have. For example, if my name shows up on the form multiple times, as does yours, with different points associated with both, what would be the best way to sort unique values and count the points? Should it be another sheet, a workflow, a report? And where would I start from a formula standpoint? Thank you! And again, I apologize for bothering you again!

Genevieve P.

Hi @MJones

I would personally set up a Report to get this data, Grouping by the Name column and then using Summarize to SUM up the points per-person.

Here's a webinar that goes through these Report functions: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve