2 way Lookup ID and another column

[ABBA]

Hi
I have a table and I am trying to get the value from column “Bbls” by looking up “Tank or Gauge Name” and user input “inches”

This is the expression

I do not get any number, and dont know what I am doing wrong

[Steve]

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[ABBA]

Thats 1 Way LOOKUP.
How do I do multiple column lookup?

Thanks
Amol

[Steve]

ABBA:

How do I do multiple column lookup?

What do you mean? Like SQL SELECT colA, colB ...? AppSheet doesn’t provide that, so you’ll just have to use multiple LOOKUP() expressions.

[ABBA]

@Steve I am referring to Index and Match function in excel.
I want to be able to match the Tank or Guage ID and the Match the Inches THROUGH the expression to get the corresponding Bbls

[Steve]

I’m still not entirely clear, but I think you want the Bbls column from a row that matches criteria that involves two other columns in the row. Based on what you’ve posted above, it appears the Bbls column is in the Tank Gauge Capacity table, and that you want to limit the search to only rows where the [Tank or Gauge Name].[Type] dereferences to the literal Text value, Oil. You also mention “user input ‘inches’”, but its not clear where the user input is coming from or how it should be interpreted.

Try this:

ANY(
  SELECT(
    Tank Gauge Capacity[Bbls],
    AND(
      ([Tank or Gauge Name].[Type] = "Oil"),
      ([Inches] = [_THISROW].[Inches])
    )
  )
)

[ABBA]

Steve:

ANY(
  SELECT(
    Tank Gauge Capacity[Bbls],
    AND(
      ([Tank or Gauge Name].[Type] = "Oil"),
      ([Inches] = [_THISROW].[Inches])
    )
  )
)

Thanks @Steve
I tried the solution you provided

Gives me an error

[Steve]

That is not the expression I provided.

[ABBA]

@Steve
Using the expression that you gave, I get the following error. So I tried to correct the error and ended using the modified expression

[ABBA]

@Steve I want the Tank Gauge Capacity[Bbls] column from a row that matches criteria that involves, Tank Gauge Capacity[Type]=Oil, Tank Gauge Capacity[ID], Tank Gauge Capacity[Inches] columns in the row

[ABBA]

@Steve @Marc_Dillon I want the Tank Gauge Capacity[Bbls] column from a row that matches criteria that involves,
Tank Gauge Capacity[Type]=Oil,
Tank Gauge Capacity[Tank or Gauge ID]=[Tank Gauge][Tank or Gauge ID]—where Tank or Gauge ID in Tank Gauge table is Ref, and 3rd criteria Tank Gauge Capacity[Inches]=[Tank Gauge][Total-Inches]

[1minManager]

You’re looking at something like this -

ANY(Select(Tank Gauge Capacity[bbs],
AND(
[Type]=Oil,
[Tank or Gauge Name]=“Fireflood”,
[Inches]=0.25
)
))

But I can’t understand what you’re trying to say with this

Tank Gauge Capacity[Tank or Gauge ID]=[Tank Gauge][Tank or Gauge ID]—where Tank or Gauge ID in Tank Gauge table is Ref

and this

Tank Gauge Capacity[Inches]=[Tank Gauge][Total-Inches]

Explain these two in more detail and we may be able to help. If the formula looks at data in other tables, then you need to send a screen show of this table too

Simon@1minManager.com

[ABBA]

sorry If I may have not been clear. I apologize.
The Data is filled by the user in Table called Tank Guage. In the Tank Guage table, I am referencing the Tank or Gauge Name to come from Tank Capacity table.
When a new TankGuage reading is to be added, the user selects the Tank or Guage Name and Tank or Gauge ID and Type in Tank Gauge table are auto populated as the values are obtained from Tank Capacity Table.
The user inputs Gauge-ft and Gauge-Inches and App calculates Total Inches.
I want to get the Bbls from the Tank Gauge Capacity table, for the calculated inches and corresponding Gauge ID or Guage Name

[1minManager]

I’m assuming

Tank Gauge[Tank or Gauge ID] = is a Ref to the Tank Gauge Capacity table

So you could use something like:

ANY(Select(Tank Gauge Capacity[bbs],
[bbls]=[_ThisRow].[Total-Inches]
))

Or try

[Tank Gauge Capacity].[bbs]

Its hard to understand the Refs between tables. Maybe you want to share you App and I’ll take a look?

[ABBA]

@Steve @Marc_Dillon Bump! Anyone…I believe someone knows

[ABBA]

Tank Gauge[Tank or Gauge ID] = is a Ref to the Tank Gauge List table

The App formula is correct, but now I do not see the Oil Column, in the Tank Gauge Form when adding new reading
Its proprietary App, sorry cant share
Thanks

[1minManager]

So if you cant see [Oil] then its probably either:
Show is turned off
You have a Show formula thats hidding it
It uses a formula that is pulling a blank result
You’re using a slice and the column isn’t in there

[ABBA]

Your Assumption that Tank Gauge[Tank or Gauge ID] = is a Ref to the Tank Gauge Capacity table is not correct

Tank Gauge[Tank or Gauge ID] = is a Ref to the Tank Gauge List table and NOT Tank Gauge Capacity , It was a Typo
I am using the expression as below

ANY(Select(Tank Gauge Capacity[bbs],
[bbls]=[_ThisRow].[Total-Inches]
)) in Tank Guage Table for Oil,bbls

[1minManager]

So its probably returning a blank result then. The formula is valid, but not finding the correct figure

[ABBA]

I believe that it is returning Blank result, I am stumped

[ABBA]

I have got this to work almost there
I am using the expression

Now this condition is to be satisfied, [Tank or Gauge Name].[Type] = “Oil”

[ABBA · Accepted solution]

I have the solution

ANY(Select(Tank Gauge Capacity[bbls],
AND(
[Type]=Oil,
[Inches]=[_ThisRow].[Total-Inches]
)))