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'.