Lookup column A then either Column B or C to find result

Welcome to the community, @David_Graham! :grinning_face_with_big_eyes: You provided some great details there, except for one thing.

How is the user selecting these options? I’m guessing you have another table, and you’re linking to this table for the contribution selection portion. But where/how are they making the Over/Under choice? All of that will play into the solution.

I’m guessing this can’t be done?

Sorry for the lack of response, David. It wasn’t because it can’t be done. I’m spinning many plates on this end, and this discussion just fell off my radar in all the chaos.

If the user selects either Over or Under, and then selects a percentage—i.e. as a link to that table—then this is a piece of cake. If they just enter a number, though, then it becomes a situation where a script will need to intervene. You can probably guess which option I recommend. :winking_face:

All of the following stuff should be done on your primary table (which you didn’t name, so I’ll just call it [Table A]). Have your {Percentage} field (or whatever it’s named) be a link to [Table B]. With that link in place, you can add two rollup fields named {Over} and {Under}, each pulling from their respective related field in [Table B] based on the link. For each rollup field’s aggregation formula, use MAX(values). Because there’s only one value coming through, it will just return that single value.

Finally, add a formula field (I’ll call mind {OverUnder Value}). I’m going to assume the field where “Over” or “Under” is chosen (i.e. as a single select) is named {OverUnder}, but obviously change this as needed. Here’s the formula:

SWITCH(OverUnder, "Over", Over, "Under", Under)

Does that make sense?

Sorry for the lack of response, David. It wasn’t because it can’t be done. I’m spinning many plates on this end, and this discussion just fell off my radar in all the chaos.

If the user selects either Over or Under, and then selects a percentage—i.e. as a link to that table—then this is a piece of cake. If they just enter a number, though, then it becomes a situation where a script will need to intervene. You can probably guess which option I recommend. :winking_face:

All of the following stuff should be done on your primary table (which you didn’t name, so I’ll just call it [Table A]). Have your {Percentage} field (or whatever it’s named) be a link to [Table B]. With that link in place, you can add two rollup fields named {Over} and {Under}, each pulling from their respective related field in [Table B] based on the link. For each rollup field’s aggregation formula, use MAX(values). Because there’s only one value coming through, it will just return that single value.

Finally, add a formula field (I’ll call mind {OverUnder Value}). I’m going to assume the field where “Over” or “Under” is chosen (i.e. as a single select) is named {OverUnder}, but obviously change this as needed. Here’s the formula:

SWITCH(OverUnder, "Over", Over, "Under", Under)

Does that make sense?

Hi Justin,

Sorry but i’m just not getting it!

these are the 2 tables, not sure if i have set them up correctly.

so the idea is when a user a percentage in “Current % Contribution” and selects either “Over” or “Under” the table should look in table “Over/Under” and find the decimal number that corresponds. ie… The user enters (or selects) 10% and selects “Over” the result should be 0.31

any assistance is very much appreciated.

Am i able to send the table if that helps?

Regards

David

The main problem here is that you’re using single-select options for the {Under} and {Over} values on the [Over/Under] table. Those should just be number fields, because you need those numbers for calculations in the other table. You can technically get numbers from these, but it’s more work than is necessary. The good news is that if you convert both of those fields to numbers, all of the values will remain intact. By doing this, the rollups in [Input Data] should work because they’re now pulling numerical values instead of the text from those single-select options.

For the primary field, I see you’re using an autonumber, which technically works, but it’s also kind of overkill. I suggest converting the {Contribution} field to a regular percentage field, then copying those values into the primary field. Delete {Contribution}, and use that name for the primary field. (These changes won’t necessarily affect the end result, but it just makes the presentation cleaner, so you can see actual percentage values in the links you make from the [Input Data] table.)