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.

0 11 646
11 REPLIES 11

Lynn
Participant V

Hi @Fof_Webmaster
I made a demo by rearranging the data. Would this be any help. Check app EcoliColonies in my portfolio
https://www.appsheet.com/portfolio/129805

Hi, Lynn. I wanted to look at your demo app (https://www.appsheet.com/portfolio/129805) but, when I select the link, I can’t find it. Is it still available?

Thanks,
Seán

Steve
Participant V

Maybe this?

SWITCH(
  [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"),
  DECIMAL("")
)

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?
Thanks
Bob

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

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:

ANY(
  SELECT(
    table[result],
    AND(
      ([SWell] = ([_THISROW].[Small Well Count]),
      ([LWell] = ([_THISROW].[Large Well Count]),
    )
  )
)

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

Big thanks as always Steve!

Bob

Lynn
Participant V

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.

Lynn
Participant V

Hi @SeanHaney
That app is no longer available.

Thanks, @Lynn, for getting back to me. I’ll try to find some examples elsewhere, then.

Top Labels in this Space