PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

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.


 

 

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.
 
AFTER CLICKING THE BUTTON ABOVE, a file named gkxlookup.xlam will ask your approval to save. Save it in the desired location.
SOMETIME 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.
The process is shown in the picture below.
HOW TO INSERT ADDIN IN EXCEL
INSTALLING ADD IN TO 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 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.

 

XLOOKUKP AVAILABLE IN EXCEL 2016
CHOOSING GKXLOOKUP FROM THE CUSTOM TAB

STEP 4:

After clicking the gkxlookup, the first window will open asking for the value or values to be looked up.

XLOOKUKP AVAILABLE IN EXCEL 2016
ENTER THE VALUE OR VAULES TO BE LOOKED UP

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.

XLOOKUKP AVAILABLE IN EXCEL 2016
ENTER THE VALUE OR VAULES 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.

XLOOKUKP AVAILABLE IN EXCEL 2016
ENTER THE VALUE OR VAULES 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.

XLOOKUKP AVAILABLE IN EXCEL 2016
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
XLOOKUKP AVAILABLE IN EXCEL 2016
EXAMPLE DATA

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.

XLOOKUKP AVAILABLE IN EXCEL 2016
ENTER THE COLUMN HEADINGS AS EMP AGE AND OVERTIME

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.

XLOOKUKP AVAILABLE IN EXCEL 2016
STEPS TO USE GKXLOOKUP

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

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]

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: