2D Lookup


2D Lookup searches within rows and columns at the same time.

So in 2D lookup the range for key values are present both in horizontal rows and in vertical columns unlike a normal lookup where the range for key values are present only in vertical columns.

SheetKraft contains the 2DLookup option which can be accessed by the Lookup Button present in the SheetKraft Toolbar.

For example, if we want to look up anything from a pivot table. Let us say we have a table depicting various interest rates of different banks corresponding to different schemes.

img1


Whole picking out specific interest rate w.r.t to the bank given a particular scheme, a 2D lookup comes in handy instead of a normal lookup in this case.

Let us see an example on how to perform a 2D lookup.We have a dataset (denoted in Blue) which contains Key Values.

img2


We also have another dataset that represents our Lookup Table (denoted in Red).

img3


We select the range containing key values first (Blue) then lookup table range containing mappings containing key values (Red).

img4


img5


The dialogue box shown below, opens:

img6


In the Row Headers section, (this represents our first argument) we can input the field using which we want to match the rows. In the Column Headers section (this represents our second argument) we can input the field using which we want to match the columns.

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

img7


After entering your choice, click Finish.

img8