Index Match help

Options

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).


Best Answers

  • Elizabeth Aird
    Elizabeth Aird ✭✭✭✭
    Answer ✓
    Options

    You are one smart man!!! :)

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Elizabeth Aird
    Elizabeth Aird ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Elizabeth Aird ✭✭✭✭
    Options

    Stage 2 FA, Stage 2 TA, Stage 2 Ratio

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Elizabeth Aird
    Elizabeth Aird ✭✭✭✭
    Options

    yes - and then the combo gives the required action.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Elizabeth Aird ✭✭✭✭
    Options

    Yes ... can it be done? :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Elizabeth Aird ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Elizabeth Aird ✭✭✭✭
    edited 04/17/20
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Elizabeth Aird ✭✭✭✭
    Answer ✓
    Options

    You are one smart man!!! :)

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!