Index Match help
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
-
You are one smart man!!! :)
I still may have questions but am going to work on it now. Thanks again!!
-
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.
Answers
-
Are you producing "Low", "High", and "Okay" in the row on your Smartsheet?
-
No .. I would have to add columns for each of those. This is a VERY long sheet anyway. I was trying to avoid that.
-
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?
-
Stage 2 FA, Stage 2 TA, Stage 2 Ratio
-
So you would need a high, low, or okay for each of those 3?
-
yes - and then the combo gives the required action.
-
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"?"
-
Yes ... can it be done? :)
-
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?
-
The hyphenated was on the original excel - that was created by another.
And yes - the hyphenated would then pull from the right column.
-
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.
-
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))
-
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.
-
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))
-
You are one smart man!!! :)
I still may have questions but am going to work on it now. Thanks again!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!