Lookup

Lookup is used to match/compare two tables against some common field(s) and extract the results pertaining to these fields.

SheetKraft contains the Lookup option which can be accessed by the Lookup Button present in the SheetKraft Toolbar ( See figure)

Case 1: Basic Example

The following guidelines describe how Lookup is used.

Step 1

Click on the Lookup Button and select the Lookup option to open this dialogue box.

Select the data which you want to match against the other sheet. These are called the Key values. There are two ways by which you can select the data: 1)By manually selecting the data from the sheet. (By dragging over the data) 2)By entering the cell indices of the starting and ending block, for example, $A$1:$G$800.

You can select Change sheet if you want to select data from some other sheet. Also, click on the My data has headers checkbox if you have selected the headers with the data.

After selecting the data, click on OK.

Step 2

Another dialogue box will open asking you to select the range containing Mappings for the Key values you earlier selected. Select the data against which you want to match the Key Values. Use Change sheet option to select data from another sheet. After selecting the data, click OK.

Step 3

The dialogue box shown below, opens:

In the Match Criteria section, you can input the field(s) using which you want to match the two tables. The first argument refers to column in lookup table and second argument refers to the key value.

You can also use Multiple matching criteria. To add a second criterion click on the + sign.

By default, multiple conditions are linked by AND ie. matching is done for all conditions and only results, satisfying all conditions simultaneously, are displayed.

SheetKraft provides the option to use Nested conditions and a variety of conditional operators to define custom Matching criteria.

To use advanced options, click on Advanced.

The layout changes to (see figure):

Notice that instead of the equal to operator we can now select from a variety of operators (see figure below).

Nested conditions comprise condition statements contained within the definition of other condition statements. There is a Plus sign on the right hand side of the conditions. To form a nested condition, click on the Plus sign besides the condition you want to nest.

Select the nested condition. Example of a nested criteria :

Step 4

After setting the Match Criteria,we need to define which columns you want to view in the result. In the section named Report the following columns, click the + sign to select a column.

Clicking on the + sign opens a menu that lists all the fields in the Lookup table. Select the required field.

Step 5

After you have added field(s) to report, you need to select the Function which you want to operate on the data.

Step 6

After you have selected the required data fields, click on Next. You will be asked where to save the result To save the data in the Existing WorkSheet, you can input the index of the starting cell where you want to copy the data. Or you can save the data in a New Worksheet.

Step 7

After entering your choice, click Finish.

Case 2: Lookup Matching Condition: Match Only Once

This will match the keys with the lookup table only once. This will return only unique matches. To understand this, lets look at an example.

Step 1

Suppose we have a dataset as shown below(blue) and we made a column with key values to look for(red). We select the range containing key values first(red) then lookup table range containing mappings containing key values(blue).

Step 2

After selecting the data and lookup table, define the matching criteria as Date in both tables as described in Case 1. Tick on Match only once.

Step 3

Select the columns to be reported(shown below) and hit Next.

We will observe that the obtained output contains some values as #N/A. This is because some of the keys are repeating so when we are using Match only once, keys are matched exhaustively to the lookup table. So some of the repeating keys return #N/A.

Second image shows the output if we didn't tick on the Match only once, which maps keys to lookup repetitively if key repeats.

Case 3: Many To One Lookup

This functionality will match many keys to one lookup and will give the potential mapping combinations. To understand this, lets look at an example.

Step 1

Below are two tables shown, key table(Red) and Lookup table(Blue). The key table contains field amount and we need to find possible combinations of key values whose sum of amount equals to the field sum in the lookup table.

Step 2

After selecting the data and lookup table, tick on Match only once. Then a new check box for Many to one lookup appears.

Step 3

Tick on Many to one lookup.

Step 4

The dropdown menu in the first parameter in many to one match criteria contains all fields from key table. The second parameter contains all fields from lookup table.

If the user do not want to add Additional matching criteria the corresponding checkbox can be unchecked.

If the user want to limit the combinations of key values to the common value of a field in key table check the Restrict matches to the common value of checkbox.

Then a field appears and you can select a field from key table from the dropdown window.

Step 5

Select the required matching criteria and report columns and click Next. The following image shows the result.