2.2-VLOOKUP, HLOOKUP & SUMIFS...

2025-08-16 11:00

Status: Child

Tags: Data Analysis Excel

VLOOKUP, SUMIF, COUNTIF, SUMIFS, INDEX & MATCH, MINIFS, MAXIFS, AVERAGEIFS

VLOOKUP Function

What is VLOOKUP?

VLOOKUP searches for a value in the leftmost column of a table and returns a corresponding value from a specified column. Think of it like searching for a contact in your phone and retrieving their phone number.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Parameters

Parameter	Description	Example
lookup_value	The value to search for	C2 (Product_ID)
table_array	The data range to search	$A$2:$F$100
col_index_num	Column number to return (1=first)	2 (Product Name)
range_lookup	FALSE = exact match, TRUE = approximate	FALSE or 0

Key Characteristics

• Direction: Searches left to right only (column-wise)
• Lookup Value: Must be in the leftmost column of table_array
• Best Use: Unique identifiers like Employee IDs, Product codes

---

When to Use VLOOKUP: Fact vs Lookup Tables

The Concept

Fact Table = Transaction data (changes frequently, contains IDs only)
Lookup Table = Reference data (changes rarely, contains detailed descriptions)
VLOOKUP = The bridge connecting them

Why Separate Tables?

❌ WITHOUT Lookup Tables:

• Data repeated hundreds of times
• Update one detail = change 100+ rows
• High risk of typos and inconsistencies
• Bloated file sizes

✅ WITH Lookup Tables:

• Data stored once, referenced many times
• Update once, applies everywhere
• Smaller files, faster performance
• Professional database structure

---

Example 1: Sales Transaction System

FACT TABLE: Daily Sales

Order_ID	Date	Product_ID	Quantity	Total_Amount
1001	2025-01-15	P101	5	$249.95
1002	2025-01-15	P103	2	$599.98
1003	2025-01-16	P102	10	$299.90
1004	2025-01-16	P101	3	$149.97

LOOKUP TABLE: Product Master

Product_ID	Product_Name	Category	Unit_Price	Supplier
P101	Wireless Mouse	Electronics	$49.99	TechCorp
P102	USB Cable	Electronics	$29.99	TechCorp
P103	Keyboard	Electronics	$299.99	KeyMaster
P104	Monitor Stand	Furniture	$79.99	OfficePro

VLOOKUP Formulas

Get Product Name:

=VLOOKUP(C2, ProductMaster!$A$2:$E$6, 2, FALSE)

Get Category:

=VLOOKUP(C2, ProductMaster!$A$2:$E$6, 3, FALSE)

Get Unit Price:

=VLOOKUP(C2, ProductMaster!$A$2:$E$6, 4, FALSE)

Results After VLOOKUP

Order_ID	Product_ID	Product_Name	Category	Quantity	Total
1001	P101	Wireless Mouse	Electronics	5	$249.95
1002	P103	Keyboard	Electronics	2	$599.98
1003	P102	USB Cable	Electronics	10	$299.90

---

Example 2: Employee Attendance System

FACT TABLE: Attendance Records

Record_ID	Date	Emp_ID	Check_In	Check_Out	Hours
A001	2025-10-01	E205	09:00 AM	05:30 PM	8.5
A002	2025-10-01	E312	08:45 AM	05:00 PM	8.25
A003	2025-10-01	E198	09:15 AM	06:00 PM	8.75

LOOKUP TABLE: Employee Master

Emp_ID	Full_Name	Department	Position	Hourly_Rate
E198	Sarah Ahmed	Sales	Executive	$25.00
E205	Karim Hassan	IT	Developer	$35.00
E312	Fatima Khan	Marketing	Manager	$45.00
E401	Rahim Uddin	Finance	Analyst	$30.00

VLOOKUP Formulas

Get Employee Name:

=VLOOKUP(C2, EmployeeMaster!$A$2:$E$6, 2, FALSE)

Get Department:

=VLOOKUP(C2, EmployeeMaster!$A$2:$E$6, 3, FALSE)

Calculate Daily Pay:

=F2 * VLOOKUP(C2, EmployeeMaster!$A$2:$E$6, 5, FALSE)

Final Report

Date	Emp_ID	Name	Department	Hours	Rate	Daily_Pay
2025-10-01	E205	Karim Hassan	IT	8.5	$35.00	$297.50
2025-10-01	E312	Fatima Khan	Marketing	8.25	$45.00	$371.25
2025-10-01	E198	Sarah Ahmed	Sales	8.75	$25.00	$218.75

---

Common VLOOKUP Errors

Error	Cause	Solution
A	Lookup value not found	Check for extra spaces with TRIM(), verify ID exists
REF!	Column index exceeds range	Count columns carefully (1, 2, 3…)
Wrong Result	Used TRUE instead of FALSE	Always use FALSE for exact matches

Error Handling

=IFERROR(VLOOKUP(A2, DataTable, 2, FALSE), "Not Found")

---

Advanced VLOOKUP Techniques

Multiple Column Returns (Array Formula)

Scenario: You want to retrieve Product Name, Category, and Price all at once.

Lookup Table:

Product_ID	Product_Name	Category	Unit_Price	Supplier
P101	Wireless Mouse	Electronics	$49.99	TechCorp
P102	USB Cable	Electronics	$29.99	TechCorp
P103	Keyboard	Electronics	$299.99	KeyMaster

Formula:

={VLOOKUP($B$2, $B$5:$F$8, {2,3,4}, FALSE)}

What it does:

• Looks up value in B2 (e.g., “P101”)
• Returns columns 2, 3, and 4 simultaneously
• Displays: “Wireless Mouse”, “Electronics”, “$49.99” in three adjacent cells

Usage: Enter in the first cell, press Ctrl+Shift+Enter (creates array formula with curly braces)

---

Dynamic Column Selection

Scenario: Create a flexible report where dragging the formula automatically retrieves different columns.

Setup:

Row 1 Headers:	Product_ID	Name	Category	Price	Supplier

Formula in B2:

=VLOOKUP($A$2, $A$5:$E$10, COLUMNS($A$1:B1), FALSE)

How it works:

• In cell B2: COLUMNS($A$1:B1) = 2 → Returns Product Name (column 2)
• In cell C2: COLUMNS($A$1:C1) = 3 → Returns Category (column 3)
• In cell D2: COLUMNS($A$1:D1) = 4 → Returns Price (column 4)

Benefit: Drag formula right and it automatically adjusts column index!

---

Solving Left-Most Column Limitation

Problem: VLOOKUP can’t look left. If you need to find Employee ID based on Employee Name:

Data Structure:

Emp_ID	Employee_Name	Department
E101	Sarah Ahmed	Sales
E102	Karim Hassan	IT
E103	Fatima Khan	Marketing

❌ This Won’t Work:

=VLOOKUP("Sarah Ahmed", B5:C8, -1, FALSE)  # Can't use negative column index

✅ Solution 1: Composite Keys (for matching multiple criteria)

Scenario: Find price based on BOTH Region AND Product.

Lookup Table with Helper Column:

Region+Product	Region	Product	Price
WestSamsung	West	Samsung	$799
EastiPhone	East	iPhone	$899
NorthDell	North	Dell	$550

Formula:

=VLOOKUP([@Region] & [@Product], A2:D5, 4, FALSE)

Example:

• Looking for: Region=“West”, Product=“Samsung”
• Formula searches for: “WestSamsung”
• Returns: $799

✅ Solution 2: Use INDEX/MATCH Instead

=INDEX(A5:A8, MATCH("Sarah Ahmed", B5:B8, 0))

This finds “Sarah Ahmed” in column B and returns the corresponding Emp_ID from column A.

Advanced VLOOKUP: Handling Multiple Matches and Errors in Excel

Introduction to the Complex Scenario

In this tutorial, we explore a more complex pricing table that includes products and region-specific prices. The table structure is as follows:

Product	Region	Price
Box	Sylhet	20
Box	Kumilla	30
…	…	…

This setup introduces challenges with VLOOKUP, particularly when dealing with non-leftmost lookup columns and duplicate product entries across regions.

Problem 1: VLOOKUP Requires the Leftmost Column for Lookup

VLOOKUP searches for a value in the leftmost column of a table and returns a value from a specified column in the same row. If the lookup value (e.g., product name) is not in the leftmost column, the function fails.

Example Issue

Suppose you attempt to look up a product with pricing in the third column:

=VLOOKUP("Box", Table, 4, FALSE)

This returns an error because the product column is not the leftmost.

Solution for Problem 1: Rearrange the Table

To resolve this, restructure the table so the lookup column (e.g., Product) is the leftmost:

Product	Price	Region
Box	20	Sylhet
Box	30	Kumilla

Now, the formula works:

=VLOOKUP("Box", Table, 2, FALSE)

This returns the price from the second column. However, this introduces a new issue with multiple rows for the same product.

Problem 2: Handling Multiple Matches for the Same Product

When the same product appears in multiple rows (e.g., different regions), VLOOKUP returns the first match it finds, ignoring subsequent entries.

Example Issue

For “Box in Kumilla,” the formula returns 20 (from Sylhet) instead of the correct 30:

=VLOOKUP("Box", Table, 2, FALSE)  // Returns 20, the first match

This leads to incorrect pricing, such as a “big loss” if the wrong regional price is applied.

Solution for Problem 2: Create Unique IDs

To differentiate entries, add a unique ID column that combines the region and product (e.g., using concatenation). This ensures each lookup is specific.

Step 1: Generate Unique IDs

In a new column (e.g., ID), create identifiers like “Sylhet-Box” or “Kumilla-Box”:

=Region & "-" & Product

Updated table:

ID	Product	Region	Price
Sylhet-Box	Box	Sylhet	20
Kumilla-Box	Box	Kumilla	30
…	…	…	…

Step 2: Perform Lookup on Unique ID

Construct the lookup value by combining region and product in the search cell, then use VLOOKUP on the ID column (now leftmost):

=VLOOKUP(Region & "-" & Product, ID_Table, 4, FALSE)

This returns the correct price for the specific region-product combination. For example:

• Input: “Kumilla-Box” → Returns 30.

If the entry doesn’t exist (e.g., “Gajipool-Box”), it returns an error, which we handle next.

Implementing the Lookup with Unique IDs

Rearrange the table to place the ID column leftmost:

ID	Region	Product	Price
Sylhet-Box	Sylhet	Box	20
Kumilla-Box	Kumilla	Box	30

The formula becomes:

=VLOOKUP(G2 & "-" & H2, ID_Table, 4, FALSE)

Where G2 is Region and H2 is Product. This setup is efficient and avoids ambiguity.

Handling Errors with IFERROR

VLOOKUP returns errors (e.g., A) for missing references. Wrap it in IFERROR to provide a custom message:

=IFERROR(VLOOKUP(G2 & "-" & H2, ID_Table, 4, FALSE), "Reference is missing. Please update")

Example Usage

• Valid lookup (e.g., “Kumilla-Box”): Returns 30.
• Invalid lookup (e.g., “Gajipool-Box”): Returns “Reference is missing. Please update”.

For divisions or other operations, extend with additional checks, but ensure proper bracketing to avoid formula errors.

Adding More Data and Final Touches

Populate the table with additional regions, such as Bogura (B-O-G-U-R-A):

ID	Region	Product	Price
Sylhet-Box	Sylhet	Box	20
Kumilla-Box	Kumilla	Box	30
Bogura-Box	Bogura	Box	25
…	…	…	…

Copy formulas down the rows (e.g., using Ctrl+Z for undo if needed). Test with various inputs to ensure accuracy.

This approach scales well for region-specific pricing. For further exploration, consider the TRUE parameter in VLOOKUP for approximate matches in the next tutorial.

HLOOKUP Function

What is HLOOKUP?

HLOOKUP (Horizontal Lookup) works exactly like VLOOKUP but searches horizontally across rows instead of vertically down columns. The “H” stands for “Horizontal.”

Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

When to Use HLOOKUP

• Data organized with categories in the first row (horizontally)
• Need to search across columns and return from rows below
• Common in: monthly reports, quarterly summaries, time-series data

Real-World Example: Quarterly Sales Report

Data Structure:

|Q1|Q2|Q3|Q4| |---|---|---|---|---| |Revenue|$450K|$520K|$610K|$580K| |Expenses|$320K|$340K|$380K|$365K| |Profit|$130K|$180K|$230K|$215K| |Growth%|5%|8%|12%|10%|

Formula to find Q3 Revenue:

=HLOOKUP("Q3", B1:E1, 2, FALSE)

Breakdown:

• "Q3" = Looking for Q3 quarter
• B1:E1 = Search in first row (quarters)
• 2 = Return value from row 2 (Revenue)
• FALSE = Exact match
• Result: $610K

Formula to find Q2 Profit:

=HLOOKUP("Q2", B1:E1, 4, FALSE)

Result: $180K

Practical Application: Employee Shift Schedule

Schedule Table:

Mon	Tue	Wed	Thu	Fri
Morning	Sarah	John	Sarah	Lisa	John
Afternoon	Lisa	Sarah	John	John	Sarah
Evening	John	Lisa	Lisa	Sarah	Lisa

Find who works Tuesday Morning:

=HLOOKUP("Tue", B1:F1, 2, FALSE)

Result: John

Find who works Friday Evening:

=HLOOKUP("Fri", B1:F1, 4, FALSE)

Result: Lisa

VLOOKUP vs HLOOKUP

Aspect	VLOOKUP	HLOOKUP
Direction	Vertical (down columns)	Horizontal (across rows)
Search Location	First column (leftmost)	First row (topmost)
Returns From	Columns to the right	Rows below
Common Use	Database-style tables	Time-series, dashboards
Data Layout	Tall tables	Wide tables

Converting Between VLOOKUP and HLOOKUP

Same Data, Different Layout:

VLOOKUP Format (Vertical):

Product	Price
Mouse	$49.99
Keyboard	$299.99

HLOOKUP Format (Horizontal):

Product	Mouse	Keyboard
Price	$49.99	$299.99

VLOOKUP Formula:

=VLOOKUP("Mouse", A2:B3, 2, FALSE)

HLOOKUP Formula (same result):

=HLOOKUP("Mouse", B1:C2, 2, FALSE)

When HLOOKUP Makes Sense

✅ Good for:

• Monthly/Quarterly reports (time periods across top)
• Comparison tables (products/competitors across columns)
• Survey results (questions across top)
• Dashboard summaries with metrics in rows

❌ Not ideal for:

• Large databases (use VLOOKUP or INDEX/MATCH)
• Data that grows vertically (adding new records)
• Complex multi-criteria lookups

Pro Tip: INDEX/MATCH Works Both Ways

Instead of choosing between VLOOKUP and HLOOKUP, use INDEX/MATCH which works for both:

Horizontal Lookup with INDEX/MATCH:

=INDEX(2:2, MATCH("Q3", 1:1, 0))

This gives you flexibility without remembering two different functions!

---

SUMIFS Function

Overview

SUMIFS sums values based on multiple criteria simultaneously.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Parameters

• sum_range: Column containing values to sum
• criteria_range1: First condition range
• criteria1: First condition value
• criteria_range2: Second condition range
• criteria2: Second condition value

Example

=SUMIFS(Sales_Column, Month_Column, "March", Customer_Column, "Samsung", Amount_Column, ">500")

Sums sales where Month=“March”, Customer=“Samsung”, and Amount>500.

Related Functions

AVERAGEIFS - Calculates average with multiple criteria:

=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

MAXIFS / MINIFS - Finds max/min with criteria:

=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MINIFS(min_range, criteria_range1, criteria1, ...)

---

Best Practices

Formula Writing

1. Use absolute references for lookup tables: $A$2:$F$100
2. Always use FALSE for exact matches in VLOOKUP
3. Add error handling with IFERROR for user-friendly results
4. Name ranges for readability: =VLOOKUP(A2, ProductList, 2, FALSE)

Data Management

5. Remove spaces with TRIM() function
6. Ensure consistent formatting (text vs numbers)
7. Use “Ctrl + T” Table Format to transform the data into table
8. Use Data Validation to prevent invalid entries
9. Keep lookup tables on separate sheets for organization

Performance

9. Use specific ranges instead of entire columns (A:A)
10. Consider INDEX/MATCH for large datasets (>10,000 rows)
11. Sort data when using approximate match

---

Practice Exercises

Exercise 1: Basic VLOOKUP

Sample Data:

Order_ID	Sales_Rep	Product	Region	Price
1019	Mahesh	Samsung	West	7677
1020	Sarah	iPhone	East	8999
1021	John	Dell	North	5500

Tasks:

1. Find the product for Order_ID 1019
2. Get the price for the iPhone product
3. Return “Not Found” if Order_ID doesn’t exist

Exercise 2: INDEX/MATCH

Using the same data:

1. Find the sales rep based on product name
2. Get the region for a specific order ID
3. Lookup price by matching both region AND product

Exercise 3: SUMIFS

Create formulas to:

1. Sum total prices for West region
2. Average price by sales rep
3. Count orders above $6000

Solutions:

Exercise 1:

1. =VLOOKUP(1019, A2:E4, 3, FALSE)
2. =VLOOKUP("iPhone", C2:E4, 3, FALSE)  
3. =IFERROR(VLOOKUP(A5, A2:E4, 2, FALSE), "Not Found")

Exercise 2:

1. =INDEX(B2:B4, MATCH("Samsung", C2:C4, 0))
2. =INDEX(D2:D4, MATCH(1019, A2:A4, 0))
3. =INDEX(E2:E4, MATCH(1, (D2:D4="West")*(C2:C4="Samsung"), 0))

Note: Exercise 2.3 requires Ctrl+Shift+Enter for array formula

Exercise 3:

1. =SUMIFS(E2:E4, D2:D4, "West")
2. =AVERAGEIFS(E2:E4, B2:B4, "Mahesh")
3. =COUNTIFS(E2:E4, ">6000")

---

Quick Reference Card

When to Use Which Function?

Scenario	Best Function
Simple lookup, data left to right	VLOOKUP
Need to lookup left of key column	INDEX/MATCH
Large datasets (10,000+ rows)	INDEX/MATCH
Sum with multiple conditions	SUMIFS
Find max/min with criteria	MAXIFS/MINIFS
Two-dimensional lookup	INDEX/MATCH
Data organized in rows	HLOOKUP

Common Formula Patterns

# Basic VLOOKUP
=VLOOKUP(A2, Table, 2, FALSE)
 
# VLOOKUP with error handling
=IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not Found")
 
# INDEX/MATCH basic
=INDEX(ReturnColumn, MATCH(LookupValue, LookupColumn, 0))
 
# INDEX/MATCH 2D
=INDEX(DataRange, MATCH(RowValue, RowRange, 0), MATCH(ColValue, ColRange, 0))
 
# SUMIFS multiple criteria
=SUMIFS(SumRange, Criteria1Range, Criteria1, Criteria2Range, Criteria2)

---

Troubleshooting Checklist

• Remove leading/trailing spaces with TRIM()
• Verify lookup value exists in lookup table
• Check data types match (text vs number)
• Confirm range_lookup is FALSE for exact match
• Use absolute references ($) for table ranges
• Count columns carefully for col_index_num
• Check for hidden characters or formatting issues
• Test with known values first

References