📊 Excel Basics #20 – HLOOKUP() Function

The HLOOKUP() function searches for a value in the first row of a table and returns a value from a specified row in the same column.

Note: HLOOKUP() is less commonly used than VLOOKUP(), but it's still useful when your data is organized horizontally.

📌 What is the HLOOKUP() Function?

HLOOKUP stands for Horizontal Lookup.

It searches horizontally across the first row and returns a value from the specified row.

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

📌 Arguments Explained

• lookup_value → The value you want to search for

• table_array → The data table

• row_index_num → The row number to return the value from

• range_lookup

• FALSE → Exact match (recommended)

• TRUE → Approximate match

📌 Example

Jan Feb Mar Apr

Sales 50000 60000 55000 70000

Profit 8000 10000 9000 12000

To find the Profit for March:

Formula:

=HLOOKUP("Mar",A1:E3,3,FALSE)

Result: 9000

📌 Using a Cell Reference

If cell G2 contains the month name:

=HLOOKUP(G2,A1:E3,3,FALSE)

Changing the month in G2 automatically returns the corresponding profit.

📌 Common Errors

❌ Searching in a row other than the first row

❌ Using an incorrect row index number

❌ Forgetting to use FALSE for an exact match

❌ Getting #N/A when the lookup value doesn't exist

Handle errors using:

=IFERROR(HLOOKUP(G2,A1:E3,3,FALSE),"Not Found")

📌 Limitations of HLOOKUP()

• Searches only in the first row

• Works only from top to bottom

• Less flexible than INDEX()/MATCH() or XLOOKUP()

• Rarely used because most Excel datasets are arranged vertically

📌 Real-World Uses

• Retrieve monthly sales or profit from summary tables

• Find quarterly performance

• Fetch values from horizontally structured reports

• Build financial summary dashboards

✅ Best Practices

• Use FALSE for exact matches

• Ensure the lookup value is in the first row

• Combine HLOOKUP() with IFERROR() for user-friendly reports

• Prefer XLOOKUP() for modern Excel workbooks, as it supports both vertical and horizontal lookups

Although HLOOKUP() is less common today, understanding it will help you work with legacy Excel files and prepare for interviews.

Double Tap ❤️ For More

🚀 𝗙𝗥𝗘𝗘 𝗙𝗿𝗲𝘀𝗵𝗲𝗿 𝗛𝗶𝗿𝗶𝗻𝗴 𝗗𝗿𝗶𝘃𝗲 | 𝗧𝗲𝗰𝗵 𝗥𝗼𝗹𝗲𝘀 𝗨𝗽 𝘁𝗼 ₹𝟭𝟮 𝗟𝗣𝗔!🔥

Internship + Pre-Placement Offer

💼 Company: GoComet
💰 Stipend: ₹30,000–35,000/Month
🚀 PPO: Up to ₹12 LPA

📍 Assessment Centres: Pune | Hyderabad | Noida | Chennai | Bangalore

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:

Full Stack Intern:- https://pdlink.in/4z3vF8o

AI First SDET Interns :- https://pdlink.in/4hS1Am2

⏳ Limited Hiring Slots Available

🚀 𝗜𝗕𝗠 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 🎓

Upgrade your tech skills with 100% FREE IBM certification courses and build a strong foundation in AI, Data Science, Cloud Computing, SQL, Python, and Machine Learning.

🎯 Perfect For
🎓 Students & Freshers
👨‍💻 Software Developers
📊 Data Analysts
🤖 AI & Data Science Aspirants
💼 Working Professionals

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/45KgqDR

🔥 Start learning today and prepare yourself for high-paying opportunities in the tech industry!

📊 Excel Basics #21 – XLOOKUP() Function

The "XLOOKUP()" function is the modern replacement for both "VLOOKUP()" and "HLOOKUP()". It is more flexible, easier to use, and solves many of the limitations of older lookup functions.

«Note: "XLOOKUP()" is available in Microsoft 365 and Excel 2021+. It is not available in Excel 2019 or earlier.»

📌 What is the XLOOKUP() Function?
"XLOOKUP()" searches for a value in one range and returns the corresponding value from another range.

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

Unlike "VLOOKUP()", you don't need to specify a column number.

📌 Example 1 – Find Employee Department
ID | Name | Department
101 | Rahul | IT
102 | Priya | HR
103 | Amit | Finance
104 | Neha | Marketing

Formula:
=XLOOKUP(103,A2:A5,C2:C5)

Result: Finance

📌 Example 2 – Using a Cell Reference

If cell E2 contains an Employee ID:

=XLOOKUP(E2,A2:A5,C2:C5,"Employee Not Found")

If the ID exists, Excel returns the department.
If it doesn't exist, Excel displays: Employee Not Found

📌 Why XLOOKUP() is Better than VLOOKUP()
✅ Looks up values from left to right and right to left
✅ No need to count column numbers
✅ Built-in if_not_found argument
✅ Works with both vertical and horizontal data
✅ More reliable when columns are inserted or deleted

📌 VLOOKUP vs XLOOKUP()
VLOOKUP()
• Searches only left to right
• Uses column index numbers
• Requires IFERROR() to handle missing values

XLOOKUP()
• Searches in any direction
• Uses lookup and return ranges
• Has built-in error handling
• Easier to read and maintain

📌 Real-World Uses
• Find employee information
• Retrieve product prices
• Match customer records
• Search invoice details
• Build interactive dashboards

📌 Common Mistakes
• Using lookup and return arrays of different sizes
• Trying to use "XLOOKUP()" in older Excel versions
• Referencing the wrong lookup range

✅ Best Practices
• Use "XLOOKUP()" instead of "VLOOKUP()" whenever available
• Use the "if_not_found" argument to display meaningful messages
• Keep the lookup and return arrays the same size
• Use structured table references for dynamic formulas

💡 Bonus Example – Return Multiple Columns
=XLOOKUP(E2,A2:A5,B2:C5)
If supported by your Excel version, this returns both the Name and Department for the matching Employee ID.

"XLOOKUP()" is one of the most valuable Excel functions for modern data analysis and is becoming the preferred lookup function across industries.

Double Tap ❤️ For More

🚀 𝗧𝗼𝗽 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 𝗔𝘀𝗸𝗲𝗱 𝗯𝘆 𝗟𝗲𝗮𝗱𝗶𝗻𝗴 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀 📊

💼 Companies hiring Power BI professionals include: Microsoft, Deloitte, Accenture, Capgemini, TCS, Infosys, Cognizant, EY, PwC, KPMG, IBM, Wipro, and many more.

✅ Frequently Asked Interview Questions
✅ Beginner to Advanced Level Coverage
✅ Improve Your Problem-Solving Skills
✅ Build Interview Confidence
✅ Prepare for Top MNC Hiring Drives

𝐋𝐢𝐧𝐤👇:-

https://pdlink.in/4xqxg6v

🔥 Master Power BI interview concepts and take one step closer to landing your dream Data Analytics job!

📊 Excel Basics #22 – INDEX() Function

The "INDEX()" function returns the value from a specific position within a range or array. It is one of the most powerful functions for advanced Excel lookups.

📌 What is the INDEX() Function?

"INDEX()" returns a value based on its row number and, when working with a 2D range, its column number.

Syntax:

=INDEX(array, row_num, [column_num])

📌 Example 1 – Basic INDEX()

Consider this data:

Employee | Department

Rahul | IT

Priya | HR

Amit | Finance

Neha | Marketing

Formula:

=INDEX(B2:B5,3)

Result: Finance

Why?

"B2:B5" contains:

1. IT

2. HR

3. Finance

4. Marketing

So "INDEX()" returns the 3rd value.

📌 Example 2 – INDEX() with Rows & Columns

Consider:

Employee | Jan | Feb | Mar

Rahul | 50000 | 55000 | 60000

Priya | 45000 | 50000 | 52000

Amit | 60000 | 65000 | 70000

Formula:

=INDEX(B2:D4,2,3)

Result: 52000

Here:

2 → 2nd row of the selected range

3 → 3rd column of the selected range

📌 Why is INDEX() Important?

"INDEX()" becomes extremely powerful when combined with "MATCH()".

Example:

=INDEX(C2:C5,MATCH(E2,A2:A5,0))

This can find a value dynamically based on another cell.

For example, if E2 = Amit, Excel finds Amit's position and returns the corresponding value from column C.

📌 INDEX() vs VLOOKUP()

VLOOKUP()

• Searches in the first column

• Returns values to the right

• Uses a column index number

INDEX()

• Can return values from any direction

• Doesn't require the lookup column to be the first column

• Works extremely well with "MATCH()"

📌 Real-World Uses

• Retrieve employee information

• Find sales values

• Build dynamic reports

• Create advanced lookup formulas

• Work with large datasets

📌 Common Mistakes

❌ Using an incorrect row number

❌ Using an incorrect column number

❌ Selecting a range that doesn't contain the required data

✅ Best Practices

• Use "INDEX()" with "MATCH()" for flexible lookups

• Use "XLOOKUP()" for simpler modern lookup requirements

• Keep your lookup ranges consistent

• Use exact matching when combining "INDEX()" with "MATCH()"

💡 Remember:

"INDEX()" answers the question:

👉 "Give me the value at this position."

When combined with "MATCH()", it becomes a powerful alternative to traditional lookup functions.

Double Tap ❤️ For More

𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 & 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 📊

Start learning with FREE courses from leading companies and build in-demand skills for 2026.

🔹 Data Analytics Essentials — Cisco
🔹 Introduction to Data Science — Cisco
🔹 Python for Data Science — IBM
🔹 Azure Data Fundamentals — Microsoft
🔹 Google Analytics — Google

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/45QpA1I

🔥 Start learning today and upgrade your resume with job-ready Data & Analytics skills!

🚀 𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 📊🔥

Build in-demand Data Analytics skills with Microsoft and strengthen your resume with FREE learning opportunities.

✅ Beginner-Friendly
✅ Learn at Your Own Pace
✅ Build Job-Ready Data Skills
✅ Improve Your Resume & LinkedIn Profile
✅ Prepare for Data Analyst & BI Careers

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4hXL4Ru

🔥 Start learning today and take your first step toward a career in Data Analytics & Business Intelligence

📊 Excel Basics #23 – MATCH() Function

The MATCH() function finds the position of a value within a range. It is especially powerful when combined with INDEX() to create flexible lookup formulas.

📌 What is the MATCH() Function?

MATCH() searches for a value and returns its relative position in a range.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

The most commonly used option is:

0 → Exact match

📌 Example 1 – Find the Position

Consider:

A

Rahul

Priya

Amit

Neha

Formula:

=MATCH("Amit",A2:A5,0)

Result: 3

Why?

Within the range A2:A5:

1️⃣ Rahul

2️⃣ Priya

3️⃣ Amit

4️⃣ Neha

So Amit is in position 3.

📌 Example 2 – Using a Cell Reference

If cell E2 contains "Priya":

=MATCH(E2,A2:A5,0)

Result: 2

This makes the lookup dynamic because changing E2 changes the result.

📌 MATCH() Match Types

The third argument controls how Excel searches.

0 → Exact match

=MATCH(E2,A2:A10,0)

Use this for most business/data analysis scenarios.

1 → Approximate match, assuming the lookup array is sorted ascending.

-1 → Approximate match, assuming the lookup array is sorted descending.

⚠️ For beginners, use 0 unless you specifically need approximate matching.

📌 INDEX() + MATCH()

This is where MATCH() becomes extremely useful.

Example:

Employee | Department | Salary

Rahul | IT | 60000

Priya | HR | 55000

Amit | Finance | 70000

Neha | Marketing | 65000

To find Amit's salary:

=INDEX(C2:C5,MATCH("Amit",A2:A5,0))

How it works:

👉 MATCH() finds Amit's position → 3

👉 INDEX() returns the 3rd value from C2:C5 → 70000

Result: 70000

📌 MATCH() vs XLOOKUP()

MATCH():

• Returns the position.

• Very useful with INDEX().

• Useful when building dynamic formulas.

XLOOKUP():

• Directly returns the matching value.

• Easier for many modern lookup tasks.

• Available in newer Excel versions.

📌 Real-World Uses

• Find the position of an employee.

• Locate a product in a list.

• Find the position of a month or column.

• Build dynamic lookup formulas.

• Combine with INDEX() for advanced data analysis.

📌 Common Mistakes

❌ Forgetting the 0 for an exact match.

❌ Using approximate matching on unsorted data.

❌ Searching in the wrong range.

✅ Best Practices

• Use 0 for exact matching in most cases.

• Combine MATCH() with INDEX() for flexible lookups.

• Keep the lookup range consistent with the data you're searching.

• Use XLOOKUP() when you simply need to return a matching value.

💡 Remember:

MATCH() answers:

👉 "Where is this value?"

INDEX() answers:

👉 "What value is at this position?"

Together, they form one of Excel's most powerful lookup combinations.

Double Tap ❤️ For More

🚀 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 𝗯𝘆 𝗧𝗼𝗽 𝗖𝗼𝗺𝗽𝗮𝗻𝗶𝗲𝘀🔥

Get FREE access to company-specific interview kits, previous questions, preparation strategies, and important resources! 👇

Google :- https://pdlink.in/4xtUyIG

Amazon :- https://pdlink.in/45Q0YWR

Microsoft :- https://pdlink.in/3Up1bha

Wipro :- https://pdlink.in/4fMo1rA

Infosys :- https://pdlink.in/3TRn8p0

📌 share it with friends preparing for placements

📊 Excel Basics #24 – INDEX() + MATCH() – Powerful Dynamic Lookup

"INDEX()" and "MATCH()" are often used together to create flexible lookup formulas. Before "XLOOKUP()", this combination was one of the most popular alternatives to "VLOOKUP()".

📌 How Does It Work?

Think of it this way:

👉 "MATCH()" → Finds where the value is.

👉 "INDEX()" → Returns what is at that position.

Together:

=INDEX(return_range,MATCH(lookup_value,lookup_range,0))

📌 Example – Find an Employee's Salary

Employee Department Salary

Rahul IT 60000

Priya HR 55000

Amit Finance 70000

Neha Marketing 65000

Suppose cell E2 contains: "Amit"

Formula:

=INDEX(C2:C5,MATCH(E2,A2:A5,0))

Result: 70000

📌 Step-by-Step

First, "MATCH()" searches for Amit:

=MATCH(E2,A2:A5,0)

Result: 3

Amit is the 3rd employee in the range.

Then "INDEX()" uses that position:

=INDEX(C2:C5,3)

Result: 70000

The combined formula performs both steps automatically.

📌 Why Use INDEX() + MATCH()?

Compared with traditional "VLOOKUP()":

✅ Can look left or right.

✅ Doesn't require a column index number.

✅ More flexible when columns are inserted or rearranged.

✅ Works well for dynamic lookup scenarios.

📌 Two-Way Lookup

"INDEX()" + "MATCH()" can also find a value based on both a row and a column.

Example:

Employee Jan Feb Mar

Rahul 50000 55000 60000

Priya 45000 50000 52000

Amit 60000 65000 70000

Suppose: E2 = Amit, F2 = Feb

Formula:

=INDEX(B2:D4,MATCH(E2,A2:A4,0),MATCH(F2,B1:D1,0))

Result: 65000

Here:

👉 First MATCH() finds the employee row.

👉 Second MATCH() finds the month column.

👉 INDEX() returns the value at their intersection.

📌 Real-World Uses

• Employee salary lookup.

• Product price lookup.

• Customer information retrieval.

• Monthly sales analysis.

• Two-dimensional reporting.

• Dynamic dashboards.

📌 INDEX + MATCH vs XLOOKUP

"INDEX() + MATCH()":

• Very flexible.

• Works in older Excel versions.

• Excellent for advanced lookup logic.

"XLOOKUP()":

• Easier to write.

• Supports built-in "not found" handling.

• Can perform both vertical and horizontal lookups.

• Preferred in newer Excel versions.

📌 Common Mistakes

❌ Forgetting the "0" in "MATCH()" for exact matching.

❌ Using ranges with different sizes.

❌ Referencing the wrong row or column range.

✅ Best Practices

• Use exact matching ("0") for most business lookups.

• Keep lookup ranges consistent.

• Use absolute references when copying formulas.

• Use "XLOOKUP()" when it provides a simpler solution.

💡 Remember:

MATCH() → Find the position

INDEX() → Return the value

INDEX + MATCH → Find the right value dynamically

Mastering this combination is an important Excel skill for data analysts and interview preparation.

Double Tap ❤️ For More

🚀 𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝟮𝟬𝟮𝟲 🎓

Want to upgrade your resume with Google skills and certifications Explore FREE learning opportunities and build in-demand skills for today's job market.

👉Artificial Intelligence & Generative AI
📊 Data Analytics
☁️ Cloud Computing
📢 Digital Marketing
🔐 Cybersecurity
💻 Tech & Career Skills

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4z9pdgf

🔥 Don't just collect certificates — build skills that can help you stand out in 2026!

📊 Excel Basics #25 – Text Functions: LEFT(), RIGHT() & MID()

Text functions are extremely useful when working with names, IDs, codes, email addresses, and other text-based data.

Three important functions to learn are:

👉 LEFT()

👉 RIGHT()

👉 MID()

📌 1. LEFT() Function

LEFT() extracts a specified number of characters from the beginning (left side) of a text string.

Syntax:

=LEFT(text, [num_chars])

Example:

=LEFT("EXCEL2026",5)

Result: EXCEL

Another example:

If A2 = "EMP-10245"

=LEFT(A2,3)

Result: EMP

📌 2. RIGHT() Function

RIGHT() extracts a specified number of characters from the end (right side) of a text string.

Syntax:

=RIGHT(text, [num_chars])

Example:

=RIGHT("EXCEL2026",4)

Result: 2026

If: A2 = "EMP-10245"

=RIGHT(A2,5)

Result: 10245

📌 3. MID() Function

MID() extracts characters from the middle of a text string, starting at a specified position.

Syntax:

=MID(text, start_num, num_chars)

Example:

=MID("EMP-10245",5,5)

Result: 10245

Here:

• 5 → Starting position

• 5 → Number of characters to extract

📌 Real-World Example

Suppose you have Employee IDs:

Employee ID

EMP-10245

EMP-10321

EMP-10456

Extract the prefix:

=LEFT(A2,3)

Result: EMP

Extract the employee number:

=RIGHT(A2,5)

Result: 10245

📌 Another Example – Product Codes

Suppose: A2 = "IND-LAP-2026"

Country code:

=LEFT(A2,3)

Result: IND

Product code:

=MID(A2,5,3)

Result: LAP

Year:

=RIGHT(A2,4)

Result: 2026

📌 Common Mistakes

❌ Using the wrong character position in MID()

❌ Forgetting that spaces count as characters

❌ Extracting a fixed number of characters when the text length varies

📌 Real-World Uses

• Extract employee IDs

• Separate product codes

• Extract country or department codes

• Clean customer data

• Process invoice numbers

• Prepare data for analysis

✅ Quick Tip

LEFT() → Extract from the left ⬅️

RIGHT() → Extract from the right ➡️

MID() → Extract from the middle 🎯

These functions are especially useful when cleaning and transforming raw data before analysis.

Double Tap ❤️ For More

🚀 DATA ANALYTICS + AI: YOUR NEXT CAREER MOVE!

Data is everywhere. The right skills can put you ahead.
Join the PW Skills Data Analytics With AI Course and learn Excel, SQL, Python, Power BI & AI tools through live sessions and real-world projects.

✨ What you get:
✅ Industry-relevant Data Analytics skills
✅ AI-powered learning
✅ Microsoft collaboration
✅ Hands-on projects
✅ Job assistance*
✅ Live classes in Hinglish

📅 Starts: 14th August 2026
⏳ Duration: 5 Months
🔥 Ready to become a future-ready Data Analyst?
👉
Enroll Now & Start Your Upskilling Journey!
https://lp.pwskills.com/data-analytics-with-gen-ai-online-course?utm_source=telegram&utm_medium=influencer&utm_campaign=deepakDAonline