How to use Two Way Lookup In Microsoft Excel

CA Mukesh Kumar

We all are finance professional, some may be in Industry, few in Practice and most of us are students to become future professionals. But whatever stage we are, everyone have to face computer screen filled with Microsoft Excel Spreadsheets, outlining with financial result, budget, forecasts and plans used to make business decision ranging from operational to tactical.

Most of us aware that Microsoft Excel can add, subtract, multiply, and divide functions. But if we able to use its advance functions like VLOOKUP, HLOOKUP, INDEX+MATCH coupled with IF function, we can able to demeanor DATA MINING within few minutes which generally takes hours to complete the task and sometime impossible if we are dealing with complex data.

In this article, I’ll try to explain something exceptional in lookup functions which is easily understandable for anyone whether new to Microsoft Excel or regular user.

TWO WAY LOOKUP IN MICROSOFT EXCEL – ADVANCE EXCEL

For this I have this I use, hypothetical, Region-wise Sales Information of M/s XYZ Limited for 2015 (link to Excel File  https://taxguru.in/wp-content/uploads/2016/09/Region-Wise-Sales-Information.xlsx )

From this information our aim is to get all region-wise sales information for the month of March and December.

Steps 1Select entire table (Short cut combination Ctrl + A)

Step 2Go to the “Create from Selection” under “FORMALAS” tab (Short cut combination Ctrl +Shift + F3)

Step 3Ensure that Top row and Left column is checked and click OK

The first three steps are just to ensure that all numeric data values have name as mentioned in their Top row and Left column. For example if you go to the name manager box (Ctrl + F3) under formula tab you will see each Microsoft Excel has defined name for all values.

For Example April has values and range.

Let me explain this in much more detail,if you go the name box and select April all the data/value in front of April will get highlighted and same will also be happened for West if you select West in name box.

Step 4Use this simple Indirect Function =INDIRECT($B18) INDIRECT(C$17) and use press Enter.

We can also use =INDIRECT(B18) INDIRECT(C17), but if we use this i.e. without putting $ sign, we will not be able to use copy + paste function accurately as range will get changed.

For example if paste this formula in D18 (December, South) it will be =INDIRECT(C18) INDIRECT(D17), Whereas we need =INDIRECT(B18) INDIRECT(D17). Same problem will also happened when we copied it to D19 (March, South)

Therefore first Indirect function we need to put $ before B column “INDIRECT($B18)”. It ensures that while using copy + paste function column will be freeze and row will be keep changing.

And for second indirect function we need to put $ sign just after “INDIRECT(C$17)” It ensures that while using copy + paste function row will be freeze and column will be keep changing.

Now all information related North, South and East gets extracted for December and March.

Note 1. for using indirect function with Name Manager we need to first ensure that reference unique column should be place at starting of the data if it is somewhere in between data i.e. we need to rearrange first.

Note 2. If there is space in name, then you need to replace space with UNDERSCORE [ _ ]because while convertingit into name,Microsoft Excel automatically convert these into underscores [ _ ]. For example in Total sales column there is space between Total sales and we convert it to in names Excel reads spaces as under score.

Therefore, it that cases we need to use UNDERSCORE otherwise indirect function will not work.

I hope you will be delighted with my articles, and if you have any query related to this article or anything in Microsoft Excel feel free to get in touch.