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

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

Thats 1 Way LOOKUP.

How do I do multiple column lookup?

Thanks

Amol

What do you mean? Like SQL `SELECT colA, colB ...`

? AppSheet doesn’t provide that, so you’ll just have to use multiple *LOOKUP()* expressions.

@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

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])
)
)
)
```

1 Like

@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

That is not the expression I provided.

@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

1 Like

@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]

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

1 Like

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

I’m assuming

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

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?

1 Like

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

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

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

1 Like

Your Assumption that * Tank Gauge[Tank or Gauge ID]* = is a Ref to the

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

I am using the expression as below

ANY(Select(Tank Gauge Capacity[bbs],

[bbls]=[_ThisRow].[Total-Inches]

)) in Tank Guage Table for Oil,bbls

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

1 Like

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