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

Table of Contents

INTRODUCTION

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

The XLOOKUP is very much advanced and versatile when compared with the previous versions of lookup functions like

VLOOKUP , HLOOKUP or INDEX-MATCH etc.


XLOOKUP function 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 which means no version earlier to OFFICE 365 like Excel 2019, 2016, 2013,2010 or 2007 have this function.

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 THE FORM OF A UTILITY FOR EASY USE.



IF YOU HAVE EXCEL 365, YOU DON’T NEED THIS ADDIN. LEARN XLOOKUP HERE.

 

WHAT IS GKXLOOKUP ?

GKXLOOKUP [ Gyankosh XLOOKUP] is a simple utility that acts like the XLOOKUP FUNCTION but it is available to all versions of EXCEL whether it is EXCEL 2019, or Excel 2016 or any previous versions.

 

WHEN TO USE GKXLOOKUP ?

The function [Utility] 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.

 

DOWNLOAD THE ADDIN FILE

 

DOWNLOAD THE ADMIN FILE

 

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

 

*DON'T WORRY ABOUT THE SECURITY WHEN DOWNLOADING AND INSTALLING THESE ADDINS . WE ARE A GROUP OF TECHNOLOGY ENTHUSIASTS AND HAVE CREATED THIS RESOURCE FOR HELPING OTHERS.

 

INSTALLING THE XLOOKUP ADDIN [GKXLOOKUP]

ADDIN is in the form of a simple excel file with a .xlam extension. Follow the steps to install the ADDIN in Excel.

THE FOLLOWING PROCESS OF INSTALLATION OF THE ADDIN IS BRIEF. IF YOU DON'T FIND IT COMFORTABLE USING THE BRIEF PROCESS, A COMPLETE DESCRIPTIVE PROCESS IS PRESENT HERE.
  • 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.

INSTALLING ADD IN TO EXCEL

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

UPDATE 1: LEARN THE DESCRIPTIVE PROCESS OF INSTALLING AN ADD-IN IN EXCEL

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

CHOOSING GKXLOOKUP FROM THE CUSTOM TAB

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

ENTER THE TEXT 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.

ENTER THE VALUE OR VALUES TO BE LOOKED UP

STEP 6:

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

ENTER THE VALUE OR VALUES TO BE LOOKED UP

STEP 7:

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

ENTER THE VALUE OR VAULES TO BE LOOKED UP



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 IDEMP NAMEEMP LOCATION
1011DENISPARIS
1021LEANDERNEWYORK
1030FRIDRICKLONDON
3021JOJOPARIS
4520JJLONDON
8745KEVINPARIS
6541CHUCKDELHI
3254CASPERMOSCOW

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

TABLE 2

EMP IDEMP AGEOVERTIME
10302512
302123NA
45202613
87452715
32542917
654125NA
10112442
10212512

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.

 

FAQs

Is XLOOKUP available for Excel 2016?

No, XLOOKUP is not available for EXCEL 2016.



Is XLOOKUP available for Excel 2019?

No, XLOOKUP is not available for Excel 2019.


XLOOKUP is not working in my Excel.

XLOOKUP is not availble for all the versions of Excel but only Excel 365. You can use the utility discussed in this article to get the required output.

Is XLOOKUP case sensitive?

No Xlookup is not case sensitive. It’ll return all the matching cases.

Which Versions of Excel support XLOOKUP?

Only Excel 365 [ Excel for Office 365 ],supports built in Xlookup. No other version of Excel have XLOOKUP.

Leave a Reply

Your email address will not be published.