Index Match help

Elizabeth Aird

I am trying to convert from Excel to Smartsheet without adding a ton of columns to make this work. I am not if Index/Match is the way to go OR VLOOKUP, etc.

Again, the entries from the operators will vary therefore the low/high specs are used in previous columns that you helped me with.

This is the excel version of ONE stage. As you can see the formulas were added below in the form of "IF" functions. If the entry is above the spec, "HIGH", etc. The row below is set to do a Vlookup on another tab with the following information:

Based on 3 column, if low/high/okay for each cell and then return a required action based on the combination of these three cells.

Is it possible to somehow create this in one column or even 2 and cross-reference another sheet?

Currently have the following for conditional formatting (on a previous thread).

Elizabeth Aird

You are one smart man!!! :)

I still may have questions but am going to work on it now. Thanks again!!

Paul Newcome

There should actually only be a small amount of adjustments made. I'll try to highlight them here...

=INDEX({Ouput}, MATCH(IF([Stage 2 FA]@row < INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)), "LOW", IF([Stage 2 FA]@row > INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)), "HIGH", "OKAY")) + "-" + IF([Stage 2 TA]@row < INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)), "LOW", IF([Stage 2 TA]@row > INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)), "HIGH", "OKAY")) + "-" + IF([Stage 2 Ratio]@row < INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)), "LOW", IF([Stage 2 Ratio]@row > INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)), "HIGH", "OKAY")), {FA-TA-RATIO}, 0))

The two bold references are all that you should need to change.

{Ouput} - This is going to be the right-hand column in your second screenshot of the original post (nevermind the typo haha).

{FA-TA-RATIO} - This is going to be the left-hand column in your second screenshot of the original post.

Other than that, you should be able to use cross sheet references you already have in place for the initial INDEX/MATCH functions, and I tried to get the column names to match what you have provided in other comments here.

Paul Newcome

Are you producing "Low", "High", and "Okay" in the row on your Smartsheet?

Elizabeth Aird

No .. I would have to add columns for each of those. This is a VERY long sheet anyway. I was trying to avoid that.

Paul Newcome

Am I correct in assuming that if [Stage 2: Temperature]@row is less than the value of INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)) then it would be considered "LOW", if [Stage 2: Temperature]@row is greater than the value of INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)) then it would be considered "HIGH", and everything in between is "OKAY"?

If that is correct, then we should be able to do this without additional columns, but it could very quickly become a rather lengthy formula.

How many stages do you have?

Elizabeth Aird

Stage 2 FA, Stage 2 TA, Stage 2 Ratio

Paul Newcome

So you would need a high, low, or okay for each of those 3?

Elizabeth Aird

yes - and then the combo gives the required action.

Paul Newcome

Ok. And can you confirm my question from above?

"Am I correct in assuming that if [Stage 2: Temperature]@row is less than the value of INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)) then it would be considered "LOW", if [Stage 2: Temperature]@row is greater than the value of INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)) then it would be considered "HIGH", and everything in between is "OKAY"?"

Elizabeth Aird

Yes ... can it be done? :)

Paul Newcome

I believe so. Let me just make sure I have it 100% clear before I dive in, because it is going to get pretty complex to be able to keep it in a single column.

You want to take this logic:

if [Stage 2: Temperature]@row is less than the value of INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)) then it would be considered "LOW", if [Stage 2: Temperature]@row is greater than the value of INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)) then it would be considered "HIGH", and everything in between is "OKAY"

And apply it to each of these individually:

Stage 2 FA

Stage 2 TA

Stage 2 Ratio

To output Low, High, or OKAY for each in a hyphen delimited string that would reflect one of the strings in the left column of the second screenshot in your original post.

You then want to take that string and pull the value from the right column of that same screenshot.

Do I have that straight?

Elizabeth Aird

The hyphenated was on the original excel - that was created by another.

And yes - the hyphenated would then pull from the right column.

Paul Newcome

Ok. Phew. Let me top off my coffee and do some testing. I am certain it can be done in one large formula, but it is going to get a little hectic. Give me a little time, and I'll get back to you once I have something.

Paul Newcome

Ok. Try something like this. Make sure column names and ranges are correct. This would be the syntax for accomplishing what you are trying to do without using helper columns...

=INDEX({Ouput}, MATCH(IF([Stage 2 FA]@row < INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)), "LOW", IF([Stage 2 FA]@row > INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)), "HIGH", "OKAY")) + "-" + IF([Stage 2 TA]@row < INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)), "LOW", IF([Stage 2 TA]@row > INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)), "HIGH", "OKAY")) + "-" + IF([Stage 2 Ratio]@row < INDEX({S2Low}, MATCH(Substrate@row, {Sub}, 0)), "LOW", IF([Stage 2 Ratio]@row > INDEX({S2High}, MATCH(Substrate@row, {Sub}, 0)), "HIGH", "OKAY")), {FA-TA-RATIO}, 0))

Elizabeth Aird

Ok.. that's huge but let me work on it and get the logic in my head. Thank you!! '

I don't think I need the match(substrate@row) for this .. and {output} means the "required action"? I am assuming so I will name it that.

Paul Newcome

You will need the MATCH functions for this.

Basically what we are doing is an INDEX/MATCH to compare the numbers listed to your low and high values. Based on this we are generating a LOW, HIGH, or OKAY.

These are entered in nested IF's to accomplish this.

=IF([1st Column Name]@row < INDEX/MATCH, "LOW", IF(1st [Column Name]@row > INDEX/MATCH, "HIGH", "OKAY"))

We do the nested IF's for each stage and enter they hyphen between.

[1st column nested IF] + "-" + [2nd column nested IF] + "-" + [3rd column nested IF]

This will generate the string of

LOW-OKAY-HIGH

HIGH-HIGH-OKAY

etc.

Then we take the string generated by the series of IF's and drop that into an INDEX/MATCH to hit against the desired output table (second screenshot in your original post).

=INDEX({Desired Output Range}, MATCH([if string], {LOW/HIGH/OKAY column}, 0))

Elizabeth Aird

You are one smart man!!! :)

I still may have questions but am going to work on it now. Thanks again!!