Operation-Lookup
Aggregation
Input: Sample file with base data.
Download sample input files here.
Goal: Apply lookup with respect to bank column. In report, column 'amount' should be sum of all the bank entries available.
Hint:
1.Select key range and lookup range as given in sample file.
2.Match criteria will be Bank(lookup table)=Bank(key values)
3.Report all column with function 'SUM'.
All data
Input: Sample file with base data.
Download sample input files here.
Goal: Apply lookup with respect to bank column. In report, get all the entires using all function.
Hint:
1.Select key range and lookup range as given in sample file.
2.Match criteria will be Bank(lookup table)=Bank(key values)
3.Report all colummn with function 'ALL'.
Add Column From Key Range
Input: Sample file with base data.
Download sample input files here.
Goal: Apply lookup with respect to bank column. In report, the column reported should be Company Name,Bank Name and Branch Area.
Hint:
1.Select key range and lookup range as given in sample file.
2.Match criteria will be Bank(lookup table)=Bank(key values)
3.Report Company,Bank and Custom column with its name Branch area (the reference for the custom column should be first cell of branch area column).
If No Match
Input: Sample file with base data.
Download sample input files here.
Goal: Apply lookup. In report column, bank name should be PNB Bank if no match found in bank column.
Match Only Once
Input: Sample file with base data.
Download sample input files here.
Goal: Apply lookup with respect to bank column. In report, the bank name in key values should match with bank name in lookup table only once.
Hint:
1.Select key range and lookup range as given in sample file.
2.Select match only once in UI.
3.Match criteria will be Bank(lookup table)=Bank(key values).
4.Report all column.
Unique Items
Input: Sample file with base data.
Download sample input files here.
Goal: Apply lookup with respect to bank column. In report, There should be all unique company names corresponding to each bank.
Hint:
1.Select key range and lookup range from given sample file.
2.Match criteria will be Bank(lookup table)=Bank(key values)
3.Report all column with function 'Unique Items'.