USE XLOOKUP IN EXCEL 2007, 2010, 2013, 2016, 2019

INTRODUCTION

XLOOKUP is a new flavored lookup function provided in OFFICE 365.

XLOOKUP is very much advanced and versatile when compared with the previous versions of lookup functions like VLOOKUP , HLOOKUP or INDEX-MATCH etc.

XLOOKUP lets us lookup in any column in the table whether it is to the extreme left of the lookup table or right or anywhere in the middle. It returns the value or the complete array at once as per the selection.

Both of these functionalities were not available in the previous versions.

But, the biggest problem is that this function is not available for any of the versions except OFFICE 365.

So, Gyankosh.net created a version of this using VBA for our readers so that they don’t miss this opportunity for using this awesome function.

This article provides you an ADD IN and tells its usage which can be used in place of the XLOOKUP FUNCTION.

THE ADD IN IS CREATED IN FORM OF A UTILITY FOR EASY USE.

WHEN TO USE GKXLOOKUP

The function is very specifically meant for the situations such as:

When you want to retrieve the information from a table by looking up any value and return a single value or a complete array. It gives CASE SENSITIVE INFORMATION such as two values differing only in the case. [ Easy and easy, cat and Cat and so on].
Any column can be looked up and any column can be retrieved.
Complete look up can be done in one go.
Use this function when the built-in functions don’t work for you as VBA functions are a bit slower inherently.

DOWNLOAD THE ADDIN

ADDIN is like a small installation to your MICROSOFT EXCEL software which adds to the capabilities of the EXCEL.

CLICK ON THE BUTTON BELOW TO DOWNLOAD THE ADDIN FOR GKXLOOKUP FUNCTION and its executing macro.

Download both the files.

AFTER CLICKING THE BUTTON ABOVE, a file named gkxlookup_addin.xlam and gkxlookup_admin.xlam will ask your approval to save. Save both the files in the desired location.

SOMETIMES, DUE TO SETTINGS ISSUE IF THE BROWSER TRIES TO OPEN THIS FILE AND SHOW YOU ABSURD CHARACTERS, RIGHT CLICK ON THE BUTTON>SAVE LINK AS>CHOOSE LOCATION AND SAVE THE FILE.

INSTALLING THE ADDIN

ADDIN is in the form of a simple excel file with a .xlam extension.

Follow the steps to install the ADDIN in Excel.

OPEN EXCEL.
Go to OPTIONS>ADDINS
Select EXCEL ADD-INS
Click GO.
A new dialog box will open as shown in the picture containing all the EXCEL ADD-INS list.
We can select the Addins we want to activate.
In our case we want to install the add in , so click BROWSE.
An OPEN FILE DIALOG BOX will open.
Choose the ADD-IN file and click OK.
The Add in will show in the list.
Select it and click ok.
Add in is installed.

For ADDING THE GKXLOOKUP TO TAB CLICK HERE.

The process is shown in the picture below.

After adding the gkxlookup_addin.xlam Addin, repeat the process to add gkxlookup_admin.xlam in the same way.

GKXLOOKUP: GENERAL INFORMATION

As ARRAY functions are used using the CONTROL SHIFT ENTER, gyankosh.net created GKXLOOKUP in a simpler way in the form of a MACRO.

LET US UNDERSTAND THE STEPS TO USE GKXLOOKUP UTILITY.

STEP 1:

Create a button temporarily to assign the MACRO GKXLOOKUP or ADD IT IN A CUSTOM TAB for future use too.

For CREATING A BUTTON

Go to DEVELOPER TAB.
Click the Button option.
Draw a button anywhere in the sheet.
Right Click the button > Assign.
The macro list will open.
Choose GKXLOOKUP.
Click OK.

For ADDING THE GKXLOOKUP TO TAB

CLICK HERE.

STEP 2:

Select the cell where you want the result.

STEP 3:

Click the custom created button or select the button from the new tab.

The field asks for the value or values to be looked up.

The following ways can be used to enter the value.

Type the value in the field.
Select all the values from the sheet itself directly and the range will be filled in the field given.

STEP 5:

After entering the LOOKUP VALUES, the next input field asks for the lookup array that is, the array from which we want to lookup the value.

We can select it on the sheet itself.

STEP 6:

The next option is select the Range for the return array or table. It can be a column or a range.

STEP 7:

The last option is to put the TEXT or MESSAGE which must be displayed if the values are not found.

GKXLOOKUP: EXAMPLE

FIND OUT THE VALUES AGAINST THE CODES FROM THE GIVEN TABLES.

Let us take the example of a coded language with the following codes.

We have two tables with some information about the different employees.

Table 1 contains the details like Employee id, employee name and employee location.

We got some additional information about the employees which is given in Table 2 shown below.

Table 2 contains additional employee details like Employee age and employee overtime.

We need to create a final table in which Table 1 contains all the information in a single table.

TABLE 1

EMP ID	EMP NAME	EMP LOCATION
1011	DENIS	PARIS
1021	LEANDER	NEWYORK
1030	FRIDRICK	LONDON
3021	JOJO	PARIS
4520	JJ	LONDON
8745	KEVIN	PARIS
6541	CHUCK	DELHI
3254	CASPER	MOSCOW

Few more information came through an email and contained the following table.

TABLE 2

EMP ID	EMP AGE	OVERTIME
1030	25	12
3021	23	NA
4520	26	13
8745	27	15
3254	29	17
6541	25	NA
1011	24	42
1021	25	12

STEPS TO SOLUTION

The first step to solve the problem is to create two more columns EMP AGE and OVERTIME in the first table i.e. TABLE 1 as shown in the picture below.

Enter the column name as EMP AGE and OVERTIME in the first table as shown in the picture above.

Click the GKXLOOKUP UTILITY from the Gyankosh.net Tab. [ or any other name as per your choice ].
The first input will be for the values to be looked up.
Select the EMP ID COMPLETE COLUMN in the table 1 which we’ll be looking for in the Table 2.
After selecting the lookup values, click ok.
Second screen will be asking for LOOKUP ARRAY.
Select EMP ID COLUMN FROM TABLE 2, the lookup array from which we need to fetch the values and click OK.
The next value to be started is to select the RETURNING ARRAY.
SELECT THE COMPLETE TABLE WHICH YOU WANT TO RETURN AGAINST THE LOOKED UP VALUES. For our values, select the columns EMP AGE and OVERTIME and click OK.
The last value is the ERROR TEXT or value which will be placed if the value is not found.
Click OK.
The values will be looked up, fetched and filled in the selected Cell in the same pattern as selected in the RETURN ARRAY OR TABLE.

LEARN

EXCEL
HTML
JAVASCRIPT
MORE TO COME…

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]