Worksheet Systems Formulas

Lookup

Lookup will allow you to take data from another table based on some criteria

Syntax:
=VLookup(lookup_value,data_table, field)

– lookup_value – a value you would want to lookup. You have to specify search criteria from the lookup table.
* Country – both tables have to have Country field
* Country == ‘UK’ – it will lookup a record where Country equal ‘UK’ across all the records
* Country == CountryOfOrigin – it will lookup a row from a table where Country equal CountryOfOrigin. So, we are not limiting your search criteria.

– Data Table – Name of DataTable. You can lookup any of three types of data table.
– Field – field name

Examples

=LOOKUP(Country, Countries, Capital)
=LOOKUP(Country == OriginCountry, Countries, Capital)
=LOOKUP(Country == ‘UK’, Countries, Capital)
=LOOKUP(Country == ‘{{Country}}’, Countries, Capital)

Multiple columns lookup value
=LOOKUP([Date == CurrentDate, Salesman], Countries, Profit)
=LOOKUP([Date, Salesman], Countries, Profit)
=LOOKUP([Date, Salesman == ‘Adam’], Countries, Profit)
=LOOKUP([Date, Salesman == ‘{{CurrentUser}}’], Countries, Profit)

SumIf

SumIf will allow you to take data from another table based on some criteria

Syntax:
=SumIf(agg_value, data_table, field)

– lookup_value – a value you would want to lookup. You have to specify search criteria from the lookup table.
* Country – both tables have to have Country field
* Country == ‘UK’ – it will lookup a record where Country equal ‘UK’ across all the records
* Country == CountryOfOrigin – it will lookup a row from a table where Country equal CountryOfOrigin. So, we are not limiting your search criteria.

– Data Table – Name of DataTable. You can lookup any of three types of the data table.
– Field – field name

Examples
=SumIf(Country, Countries, Capital)
=SumIf(Country == OriginCountry, Countries, Capital)
=SumIf(Country == ‘UK’, Countries, Capital)
=SumIf(Country == ‘{{Country}}’, Countries, Capital)

Multiple columns lookup value
=SumIf([Date == CurrentDate, Salesman], Countries, Profit)
=SumIf([Date, Salesman], Countries, Profit)
=SumIf([Date, Salesman == ‘Adam’], Countries, Profit)
=SumIf([Date, Salesman == ‘{{CurrentUser}}’], Countries, Profit)