XLOOKUP in Excel: 3 Powerful Tricks for Multiple Columns (Hindi)

XLOOKUP in Excel: 3 Powerful Tricks for Multiple Columns (Hindi Tutorial)

XLOOKUP in Excel is one of the most useful functions for finding information in large datasets. Whether you want to retrieve an employee’s name, salary, department or several details at once, XLOOKUP can help you perform these tasks with a single formula.

But many Excel users know only the basic lookup formula. What happens when you want to return multiple columns together or select columns that are not next to each other?

In this Hindi video tutorial, EduTaxTuber explains 3 powerful XLOOKUP tricks using a practical employee database.

You’ll learn how to perform single-column lookups, return multiple columns using one formula, and combine XLOOKUP with the CHOOSE function to retrieve non-adjacent columns.

Video details

• Duration: 13 minutes 6 seconds
• Language: Hindi
• Original video published: November 30, 2025
• Platform: YouTube
• Presented by: EduTaxTuber

What You’ll Learn in This XLOOKUP Tutorial

By the end of the tutorial, you’ll understand how to:

1. Use XLOOKUP to find employee details using an Employee ID.
2. Retrieve multiple related columns through a single XLOOKUP formula.
3. Combine XLOOKUP and CHOOSE to return selected non-adjacent columns.
4. Convert an ordinary Excel dataset into a structured Table.
5. Use structured references instead of repeatedly selecting cell ranges.

These techniques are particularly useful for MIS reporting, employee databases, payroll analysis and other office-related Excel tasks.

What Is the XLOOKUP Function in Excel?

XLOOKUP is an Excel lookup and reference function that searches for a specified value and returns corresponding information from another range or array.

Unlike VLOOKUP, XLOOKUP does not require you to specify the numerical position of the return column. Instead, you directly select the range containing the information you want to retrieve.

Its basic syntax is:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

Here is what the main arguments mean:

By default, XLOOKUP performs an exact match, making it convenient for employee IDs, product codes and similar records.

Excel compatibility: XLOOKUP is available in Microsoft 365 and supported modern Excel versions, including Excel 2021 and Excel 2024. It is not available natively in Excel 2016 or Excel 2019.

First, Convert Your Excel Data into a Table Using Ctrl + T

Before demonstrating the three tricks, the video introduces a useful preparation step: converting the employee dataset into an Excel Table.

The example dataset contains employee-related information such as Employee ID, Name, Department, Salary, City, Age, Joining Date, Email and Manager.

Step-by-Step Instructions

Step 1: Open the worksheet containing your employee database.

Step 2: Select the dataset, including its column headings.

Step 3: Press Ctrl + T on your keyboard.

Step 4: Confirm that My table has headers is selected.

Step 5: Click OK to create the Excel Table.

Step 6: Open the Table Design tab and rename the table to EmpData.

You can change the Table Style if you don’t want the default coloured appearance. Changing the visual style does not remove its Table functionality.

Why Create an Excel Table?

Excel Tables support structured references. Instead of manually referring to a column using a range such as A2:A100, you can refer to it using a meaningful name such as:

EmpData[Employee ID]

This makes formulas easier to understand and maintain, especially when your dataset grows.

For the examples below, assume that the table is named EmpData, its headers use the exact names shown in the formulas, and the Employee ID you want to search is entered in cell A9.

Trick 1: XLOOKUP for a Single Column

The first technique demonstrates how to retrieve an employee’s name using their Employee ID.

Imagine you have an employee database containing hundreds of records.

You enter an Employee ID into cell A9 and want Excel to return the corresponding employee name.

XLOOKUP Formula

=XLOOKUP(A9,EmpData[Employee ID],EmpData[Name],"Not found")

How This Formula Works

• A9 contains the Employee ID to search for.
• EmpData[Employee ID] identifies the column containing Employee IDs.
• EmpData[Name] tells Excel to return the matching employee’s name.
• "Not found" is displayed if the ID does not exist.

For example, if employee ID 102 belongs to Ananya Rao, entering that ID will return the employee’s name.

You can update the lookup value to retrieve information for another employee without rewriting the formula.

Practical Applications

This XLOOKUP approach is helpful when retrieving employee names, product descriptions, supplier details or customer records.

Trick 2: XLOOKUP for Multiple Columns

Now consider a different situation.

Instead of retrieving only an employee’s name, you want several details at once, such as:

• Name
• Department
• Salary
• City
• Age
• Joining Date
• Email

Writing a separate lookup formula for every field is possible, but it may be unnecessary.

XLOOKUP can return multiple adjacent columns with a single formula.

XLOOKUP Formula for Multiple Columns

=XLOOKUP(A9,EmpData[Employee ID],EmpData[[Name]:[Email]],"Not found")

How the Formula Works

The lookup value is still the Employee ID entered in A9.

However, instead of selecting only the Name column, the return array includes every column between Name and Email, inclusive.

Excel returns the matching employee’s information across adjacent cells, provided enough empty cells are available.

This behaviour is known as a dynamic array spill.

Why This Method Is Useful

Suppose you are preparing an employee information dashboard.

You can select an Employee ID from a dropdown and display several details without maintaining a separate formula for every individual field.

The formula is especially useful for employee records, MIS reports, inventory dashboards and administrative reporting.

Important: The multiple-column formula should be placed in a normal worksheet range outside an Excel Table because spilled array formulas cannot expand within Excel Tables.

Also, clear the destination cells so the formula doesn’t return a #SPILL! error.

Trick 3: XLOOKUP for Non-Adjacent Columns Using CHOOSE

This is the most advanced technique demonstrated in the video.

Sometimes, the columns you want are not next to each other in the source dataset.

For example, your source table might contain:

Employee ID | Name | Department | Salary | City | Age | Joining Date | Email | Manager

But your final report needs only:

Name | Salary | Department | City

These fields are not arranged in the same order as the source data.

How can you retrieve them using one formula?

The solution demonstrated in the tutorial is to combine XLOOKUP with the CHOOSE function.

XLOOKUP and CHOOSE Formula

=XLOOKUP(A9,EmpData[Employee ID],CHOOSE({1,2,3,4},EmpData[Name],EmpData[Salary],EmpData[Department],EmpData[City]),"Not found")

Understanding the Formula

XLOOKUP searches for the Employee ID.

CHOOSE constructs the return array from four selected columns in your chosen order.

The expression {1,2,3,4} specifies the four positions in that newly constructed array.

It does not refer to the physical column numbers in the original employee database.

The selected columns are:

1. Name
2. Salary
3. Department
4. City

When XLOOKUP finds the matching Employee ID, the results are returned horizontally in that order.

Why This Trick Matters

This technique is useful when the report layout is different from the original dataset.

For example, payroll teams may need Name, Salary and Department, while administration teams may need Name, City and Manager.

Instead of rearranging the original source data, you can construct a custom return array using CHOOSE.

This is particularly valuable when building recurring reports from a shared employee database.

XLOOKUP vs VLOOKUP: What’s the Difference?

Both XLOOKUP and VLOOKUP retrieve information from tables, but their behaviour differs.

For users with a supported modern version of Excel, XLOOKUP is often more flexible for complex lookup tasks.

However, VLOOKUP remains useful when working with older Excel versions.

Common XLOOKUP Errors and How to Fix Them

Even a correctly designed XLOOKUP formula can produce an error if the input data or formula references are incorrect.

1. #N/A Error

This usually occurs when no matching lookup value is found and you have not supplied an alternative result.

Check the Employee ID and confirm that the lookup column contains the expected value.

2. #NAME? Error

Excel may not recognise XLOOKUP if you’re using an unsupported version or if the function name is misspelled.

Check the Excel version first.

3. #SPILL! Error

This can occur when a multiple-column formula needs to return results into cells that are already occupied.

Clear the intended output area and make sure you have placed the formula outside an Excel Table.

4. Incorrect Results

Check that the lookup and return arrays correspond to the same dataset and have compatible dimensions.

Also verify that Employee IDs are consistently stored as numbers or text. A mismatch may prevent otherwise identical-looking IDs from matching.

Video Chapters: Jump Directly to Each XLOOKUP Trick

Use these timestamps to navigate to the relevant section of the tutorial.

• 00:00 – Introduction to XLOOKUP
• 00:31 – Overview of the three techniques
• 01:05 – Create an Excel Table using Ctrl + T
• 02:00 – Name the employee data table
• 02:32 – Trick 1: Single-column lookup
• 05:36 – Trick 2: Multiple-column lookup
• 09:16 – Trick 3: Non-adjacent columns
• 10:21 – Combine XLOOKUP with CHOOSE
• 12:38 – Recap and conclusion

Practise More Excel Skills with EduTaxTuber

Want to explore another practical Excel technique?

Watch our ChatGPT in Excel: 5 Powerful AI Tricks tutorial to learn how AI can help generate formulas and sample datasets.

You can also try the EduTaxTuber AI Excel Formula Generator to experiment with Excel formulas.

For beginners, our guide to opening Microsoft Excel explains the basic setup and ways to launch Excel on Windows.

If you would like the practice dataset demonstrated in the video, leave a comment on the original XLOOKUP tutorial on YouTube.

Frequently Asked Questions

What is XLOOKUP in Excel?

XLOOKUP is a lookup and reference function that searches for a value and returns related information from another range or array.

Can XLOOKUP return multiple columns?

Yes. You can provide a multi-column return array, and modern Excel versions can spill the results into adjacent cells.

How do I use XLOOKUP for non-adjacent columns?

You can combine XLOOKUP with CHOOSE to construct a return array from selected columns, as demonstrated in the tutorial.

Is XLOOKUP better than VLOOKUP?

XLOOKUP is generally more flexible because it supports lookups in either direction and does not require a fixed return-column index. VLOOKUP is still important for compatibility with older Excel versions.

Does XLOOKUP work in Excel 2019?

No. Microsoft does not provide native XLOOKUP support in Excel 2019 or Excel 2016. Use Microsoft 365 or a supported newer Excel version.

Why is my XLOOKUP formula showing #SPILL!?

A multiple-result formula may be unable to expand because its destination cells are occupied, merged or otherwise unavailable.

Conclusion

XLOOKUP in Excel becomes much more powerful when you go beyond ordinary one-column lookups.

In this Hindi tutorial, we explored three practical techniques: retrieving a single column, returning multiple adjacent columns, and combining XLOOKUP with CHOOSE for non-adjacent columns.

These techniques can simplify employee databases, reporting templates and everyday spreadsheet tasks.

Practise each formula using your own sample dataset and understand the role of the lookup and return arrays before using the formulas in important workbooks.

For more Excel tutorials in Hindi, subscribe to EduTaxTuber on YouTube.

Disclaimer

This tutorial is provided for educational and informational purposes. Some accompanying written content has been prepared with AI assistance and reviewed using the video transcript and Microsoft documentation. Excel results may vary depending on software versions, workbook structure and data quality. Readers should independently verify formulas and calculations before using them for business, payroll, financial or other important purposes. EduTaxTuber and its contributors are not responsible for losses arising from errors or reliance on these examples, to the extent permitted by applicable law.