How to Filter Table Values by Column Name (in Formulas)

I am trying to lookup a column of values from a table dynamically based on other functions. I can easily get the correct column name for my table, but I am unable to get a Filter, Lookup, or anything else I have tried to return the values and not just a list of rows.

This (editable) doc shows what I’m talking about. Feel free to play around in there and hopefully you can tell me what I’m getting wrong.

Thanks,
Peter

@Peter_Williams

to get a value out of specific column after filter/lookup etc, you could use . for eg. Table1.Filter(<critieria>).Column1 would first filter set of records from Table1 based on critieria you supplied and then create a list of values from Column1 using that filtered set of records.

Thanks.

Thanks for the reply!

The problem I have is that I don’t always want Column 1, so I need to be able to dynamically choose Column 1/2/3 (ideally within the Filter or whatever function).

The result of this function that I’m trying to make is for a Select List which will have different options depending on other criteria in the app.

I was thinking I might be able to do this with yet another table that dynamically links to different columns and then I can always select “Column 1” of that table. I’m not sure if that will hit the same issues or not though.

Peter

Could you please tell me more about your scenario. Dynamically picking column isnt something supported straightaway (you could always use SwichIf function to switch on your criteria btw). so wondering if i could hear the scenario and could give you an alternative.

I updated that test doc to be closer to what I’m looking for. The end application is to have a single UI with multiple options that sort of progressively filter down. So the first selection the user makes limits the choices in the second, then that selection controls the third…

Instead of having ~100 items in a Select list to scroll through I can have a Select with 5 high-level options, then one with 4 mid-level sub-options, and finally only 5 of the original 100 items that are based on the first two answers.

okay got it, you want a cascading controls - classic example is Car Manufacturer, Car Model, Trim - where each select would reduce down option in other one. let me build an example and put it here.

That’s correct, and a good example!

I saw your Section 2 solution and that will work, although I’m worried about the scalability.

Is there another data format that would work better? Having the data in labeled rows and trying to use “thisRow” lookups? I didn’t go down that road yet because I figured this column select would work somehow I just wasn’t seeing.

I can sort of morph the data however ahead of time, but I’m hoping to avoid having to make a new Table/formula entry/etc. for each new list item I add. In the end the user will be able to add their own items to the list and I don’t want to make them edit a formula or know to copy and paste a whole structure to put data into (although maybe I could formify that?).

Thanks!
Peter

@Krunal_Sheth Made another section in the example doc linked that fixes my concerns from the message above. So now there are a couple of solution options shown in there.

Using the example of Car Company/Model/Trim the gist of the functions is:
Cars.Filter(Company=carCompanySelect.Value and Model= carModelSelect.Value).Trim.Unique(),
‘Select Car Company and Model First’

The table has to have a unique line for the lowest level with potentially repeating entries at the higher ones.
So in my ~100 items case the table may end up being ~500 lines or more, but that’s pretty easy to set up from the beginning and much better than the eye sore of a long list for the user.

Thanks again!
Peter