XLOOKUP Function

 

Introduction

The XLOOKPUP function In MS Excel is a powerful search tool, allowing to find a particular value from the cells range. Excel’s new XLOOKUP will replace VLOOKUP, providing a powerful replacement to one of Excel’s most popular functions. This new function solves some of VLOOKUP’s limitations and has extra functionality also. XLOOPUP acts as a custom search tool, flexible to the demand of different types of data that may be wider than the VLOOKUP.  

Working area of XLOOKUP?

The new XLOOKUP function has solutions for some of the biggest limitations of VLOOKUP and HLOOKUP. For example, XLOOKUP can look to its left, defaults to an exact match and allows you to specify a range of cells instead of a column number. VLOOKUP is not able to do such a task. The problem with VLOOKUP (vertical lookup) and HLOOKUP (horizontal lookup) formulas is that these formulas are limited in the sense as the long list of names suggests, they are pretty limited in scope, forcing you to use workarounds to work with data to the left or right of values. You can bypass some of these limitations with other functions (like INDEX and MATCH) but the process isn’t efficient. XLOOKUP is the solution to this problem, allowing you to look up data in columns and rows both to the left or right of your search value. It can handle approximate matches, multiple search values, nested queries, custom error messages and much more.

How to Use the XLOOKUP Function

To explain the XLOOKUP function properly, we’ll use an example. In the Excel spreadsheet contains the staff list of a company with the heading contains ID, names, email addresses and Job Title.

A data can be used as a searchable directory in Excel using an XLOOKUP formula. A search value for this formula could contain any of the values above, from an ID to an email address. XLOOKUP can find a value (matching in full or the closest i.e. approximation) from a range of cells or search array and return it. To do this, a formula using XLOOKUP has a number of arguments that need to be used to create the query.

The result will be


How the formula works

In this formula, there are three criteria: lookup_value, lookup_array, return_array. The first H2 represents the value that we want to search from the list or a table. The second criteria is the lookup array here is the ID i.e. A2 to A11 from where we search the value and the third and the most important point is the return array i.e. C2 to C11 and here is the list of Emails and we have to find the appropriate email Id regarding that searched value.  

Purpose

So, the main purpose of the XLOOKUP value to find the exact lookup value from the defined range as in the above example is from A2 to C11.

Return Value

Matching value from the defined range as C2 in the example.

Syntax

=Xlookup(lookup_value, lookup_array, return_array,[not_found],[match_mode],[search_mode])

Detail of Syntax

1.    Lookup value – that must be the value on the basis of you find the result

2.    Lookup array – this is the range or array to search

3.    Return array – this is the range or array from where you find or return the desired result.

4.    Not fond – the value to return if no match found but see carefully this is within brackets [ ], means this portion of the formula is optional and your formula will work without this portion.

5.    Match mode – it display 4 results. 0 that will return the default value, -1 that will return exact match or next smallest, 1 that will return exact match or largest and 2 is wildcard match (* of ?). Again this is also optional. As in the above example, it returns the result of the value 101 that is the exact match value if we define here 0 but if we use here wildcard match as m* instead of 101 then it will return the result 2 because there are two names Manish and Mukesh and both employees’ ID starts with the letter m. 

6.    Search mode – this is the last criteria of XLOOKUP Syntax. It also provides 4 options: 1 will return the default value. -1 will return the reverse value from the last. 2 will return the Binary search value sorted in ascending order and the last is -2 will return the Binary search value sorted in descending order.

Advantages

As it is the only key of both of the functions i.e. VLOOKUP and HLOOKUP. This only function has many advantages as

1.    It can find data from both sides as left or right

2.    It can find data horizontally or vertically

3.    It can find multiple results at the same time

4.    Its default value is exact match but VLOOKUP returns (default value) the approximate match

5.    It can also perform a reverse search means from last to first

6.    This returns #N/A if no data found

Summary

 The Excel function XLOOKUP is the latest function that is the most powerful, flexible and even we can say is the wonderful replacement function of VLOOKUP, HLOOKUP and LOOKUP. XLOOKUP supports approximate or exact matching, wildcard (* of ?). it is a single solution for VLOOKUP and HLOOKUP functions that means it can return value vertically or horizontally. XLOOKUP can search data from the first value or from the last value.      

May be interested:

How to create Attendance sheet in Excel






Comments

Post a Comment

Popular posts from this blog

TDS on Salary – Entry in Tally

Image
  Introduction TDS is Tax Deducted at Source. TDS is the amount of tax which is deducted by the employer from the taxpayer and is deposited to the Income Tax Department on behalf of him/her. The TDS rates are set on the basis of the age and income of different individuals. TDS or Tax Deducted at Source is a specific amount that is reduced when a certain payment like salary, commission, rent, interest, professional fees etc. is made. The person who makes the payment deducts tax at the source while the person who receives a payment/income has the liability to pay tax. It lowers tax evasion because the tax will be collected at the time of making a payment. Note: TDS is nothing but advance payment of income tax. When the TDS applied When you get your salary, TDS is deducted from your salary. Let’s for simplicity assume that the TDS on your salary is ₹20,000 and you are applying for the refund of the TDS because your income i.e. your salary is under the base limit for income tax wh
Read more

Post Dated Cheque (PDC) Management

Image
  Introduction Post Dated Cheque is an essential part of today’s business world. For a better financial planning and fund management PDC cheque is a must tool to survive in a tight financial conditions of a competitive world. A cheque in which a future date is written on its face is called Post Dated Cheque or popularly known as PDC. PDC can be issued to your suppliers and can be received from your customers. Normally Post-dated cheques are presented on the date of Cheque and booked or accounted on the same date. If you have a large number of PDC to manage then it is annoying for accountant to keep track of cheques. Tally ERP 9 take charge of the PDC Management for you and let you do a tension free accounting. You can enter post-dated cheques into receipt voucher, payment voucher and contra vouchers. Benefits of Post-dated Cheque Transactions 1.    You can enter the cheque into tally immediately after receiving or issuing it. 2.    You can mark a cheque as post-dated which wi
Read more

Personal Accounts in Tally

Image
 Introduction You can easily maintain personal accounts in Tally. To maintain personal accounts in Tally is very easy and simple. It is much faster than maintaining accounts for a company. The reason is that personal accounts are comparatively very small and involve only a few amounts of entries. Now compare that to a fully-fledged company which is selling products and includes all kinds of accounting entries and on the top of that, there is GST accounting entries too. But when you maintain personal accounts in Tally, there is generally no GST involved and you need to just focus on your balance sheet. So, let’s get started step by step. Maintain Personal Accounts in Tally When you have to maintain personal accounts in Tally, you should mainly focus on balance sheet. There is a specific reason for that. In personal account, there is no business account involved because it is your personal accounts. Therefore, there is NO need for a Profit and Loss Account. If you have a business, It
Read more