Lookup value in table based on user input of row and column values

I have a table called E.Coli CFU with rows labeled “E.Coli Large Wells” (0-10 in the picture) and columns labeled “E.Coli Small Wells” (0-3 in the picture). The user enters two numbers, the count of fluorescing Small Wells and Large Wells into a form. I then need to lookup in the E.Coli CFU table the corresponding E.Coli CFU level. For example if Large Wells = 2 and Small Wells = 1 I want to return the value 3.04. I can’t seem to get any formula to work.

Hi @Fof_Webmaster
I made a demo by rearranging the data. Would this be any help. Check app EcoliColonies in my portfolio


Maybe this?

  [Count of Small Wells],
  0, LOOKUP([_THISROW].[Count of Large Wells], "E.Coli CFU", "LWells", "0"),
  1, LOOKUP([_THISROW].[Count of Large Wells], "E.Coli CFU", "LWells", "1"),
  2, LOOKUP([_THISROW].[Count of Large Wells], "E.Coli CFU", "LWells", "2"),
  3, LOOKUP([_THISROW].[Count of Large Wells], "E.Coli CFU", "LWells", "3"),

Thanks Steve,
The actual table has 49 columns for “count of small wells” so the switch expression would get very large, but may still work fine. But is there a way to simplify?

Not so long as your data is structured as it is. Did you look at @Lynn’s suggestion?

1 Like

49 columns makes it a bit more work. What formula is used to calculate the results for the table?

The formula is proprietary and not available, which is too bad because this would be straightforward. But I’ll search again.

1 Like

Structure your table like this:

LWell SWell Result
0 0 0.00
1 0 1.00
2 0 2.02

Then you can get the result with a query like:

      ([SWell] = ([_THISROW].[Small Well Count]),
      ([LWell] = ([_THISROW].[Large Well Count]),
1 Like

I reformatted the table and with a few tweaks your query works like a charm.

Big thanks as always Steve!


1 Like