Monday, 27 January 2025

Here are 20 examples of how the VLOOKUP

 Here are 20 examples of how the VLOOKUP function can be used in Excel, along with sample datasets. These examples cover a variety of use cases, from simple lookups to more advanced scenarios.

Example 1: Basic VLOOKUP

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=VLOOKUP(2, A2:B4, 2, FALSE)

Result: Bob
Explanation: Looks up the name for ID 2.

Example 2: VLOOKUP with Exact Match

Dataset:

ProductPrice
Apple1.2
Banana0.8
Orange1.0

Formula:

excel
Copy
=VLOOKUP("Banana", A2:B4, 2, FALSE)

Result: 0.8
Explanation: Finds the price of a Banana.

Example 3: VLOOKUP with Approximate Match

Dataset:

ScoreGrade
0F
60D
70C
80B
90A

Formula:

excel
Copy
=VLOOKUP(85, A2:B6, 2, TRUE)

Result: B
Explanation: Finds the grade for a score of 85 using an approximate match.

Example 4: VLOOKUP with Multiple Columns

Dataset:

IDNameAgeCity
1Alice25New York
2Bob30London
3Charlie35Paris

Formula:

excel
Copy
=VLOOKUP(3, A2:D4, 4, FALSE)

Result: Paris
Explanation: Retrieves the city for ID 3.

Example 5: VLOOKUP with Dynamic Column Index

Dataset:

IDNameAgeCity
1Alice25New York
2Bob30London
3Charlie35Paris

Formula:

excel
Copy
=VLOOKUP(2, A2:D4, MATCH("Age", A1:D1, 0), FALSE)

Result: 30
Explanation: Uses MATCH to dynamically find the column index for "Age".

Example 6: VLOOKUP with Wildcard

Dataset:

ProductPrice
Apple1.2
Banana0.8
Orange1.0

Formula:

excel
Copy
=VLOOKUP("B*", A2:B4, 2, FALSE)

Result: 0.8
Explanation: Uses a wildcard to find the price of a product starting with "B".

Example 7: VLOOKUP with IFERROR

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=IFERROR(VLOOKUP(4, A2:B4, 2, FALSE), "Not Found")

Result: Not Found
Explanation: Returns "Not Found" if the lookup value doesn't exist.

Example 8: VLOOKUP with Multiple Criteria

Dataset:

IDNameDepartment
1AliceHR
2BobIT
3CharlieHR

Formula:

excel
Copy
=VLOOKUP(1 & "HR", A2:A4 & C2:C4, 2, FALSE)

Result: Alice
Explanation: Combines ID and Department to create a unique lookup value.

Example 9: VLOOKUP with Table Array from Another Sheet

Dataset (Sheet2):

IDName
1Alice
2Bob
3Charlie

Formula (Sheet1):

excel
Copy
=VLOOKUP(2, Sheet2!A2:B4, 2, FALSE)

Result: Bob
Explanation: Looks up data from another sheet.

Example 10: VLOOKUP with Named Range

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=VLOOKUP(3, Employees, 2, FALSE)

Result: Charlie
Explanation: Uses a named range "Employees" for the table array.

Example 11: VLOOKUP with Partial Match

Dataset:

ProductPrice
Apple1.2
Banana0.8
Orange1.0

Formula:

excel
Copy
=VLOOKUP("App", A2:B4, 2, FALSE)

Result: 1.2
Explanation: Finds the price for a product containing "App".

Example 12: VLOOKUP with Dynamic Lookup Value

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=VLOOKUP(A2, A2:B4, 2, FALSE)

Result: Alice
Explanation: Uses a cell reference for the lookup value.

Example 13: VLOOKUP with Multiple Results

Dataset:

IDNameDepartment
1AliceHR
2BobIT
3CharlieHR

Formula:

excel
Copy
=VLOOKUP("HR", C2:C4, 1, FALSE)

Result: 1
Explanation: Finds the first ID in the HR department.

Example 14: VLOOKUP with Date Lookup

Dataset:

DateSales
2023-01-01100
2023-02-01200
2023-03-01300

Formula:

excel
Copy
=VLOOKUP("2023-02-01", A2:B4, 2, FALSE)

Result: 200
Explanation: Looks up sales for a specific date.

Example 15: VLOOKUP with Text and Numbers

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=VLOOKUP("2", A2:B4, 2, FALSE)

Result: Bob
Explanation: Looks up a numeric value stored as text.

Example 16: VLOOKUP with Duplicate Values

Dataset:

IDName
1Alice
2Bob
2Charlie

Formula:

excel
Copy
=VLOOKUP(2, A2:B4, 2, FALSE)

Result: Bob
Explanation: Returns the first match for duplicate IDs.

Example 17: VLOOKUP with Case-Sensitive Lookup

Dataset:

ProductPrice
Apple1.2
apple0.8
Orange1.0

Formula:

excel
Copy
=VLOOKUP("apple", A2:B4, 2, FALSE)

Result: 0.8
Explanation: Matches the exact case of the lookup value.

Example 18: VLOOKUP with Array Formula

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=VLOOKUP({1,2}, A2:B4, 2, FALSE)

Result: Alice, Bob
Explanation: Returns multiple results using an array formula.

Example 19: VLOOKUP with Dynamic Table Array

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=VLOOKUP(2, INDIRECT("A2:B4"), 2, FALSE)

Result: Bob
Explanation: Uses INDIRECT to dynamically reference the table array.

Example 20: VLOOKUP with Error Handling

Dataset:

IDName
1Alice
2Bob
3Charlie

Formula:

excel
Copy
=IF(ISNA(VLOOKUP(4, A2:B4, 2, FALSE)), "Not Found", VLOOKUP(4, A2:B4, 2, FALSE))

Result: Not Found
Explanation: Handles errors using ISNA.

These examples demonstrate the versatility of the VLOOKUP function in Excel.

on No comments:

sumif and sumifs 20 examples

 The SUMIF function in Excel is used to sum the values in a range that meet a specified condition. Below are 20 examples of how to use SUMIF with sample data. Each example includes a description of the scenario, the formula, and the expected result.

Example Data

Assume the following data is in an Excel sheet:

AB
ProductSales
Apple100
Banana200
Apple150
Orange300
Banana250
Apple50
Orange400
Banana100
Apple200
Orange350

Examples

  1. Sum sales for "Apple"

    • Formula: =SUMIF(A2:A11, "Apple", B2:B11)

    • Result: 500 (100 + 150 + 50 + 200)

  2. Sum sales for "Banana"

    • Formula: =SUMIF(A2:A11, "Banana", B2:B11)

    • Result: 550 (200 + 250 + 100)

  3. Sum sales for "Orange"

    • Formula: =SUMIF(A2:A11, "Orange", B2:B11)

    • Result: 1050 (300 + 400 + 350)

  4. Sum sales greater than 200

    • Formula: =SUMIF(B2:B11, ">200")

    • Result: 1650 (250 + 300 + 400 + 350 + 200 + 150)

  5. Sum sales less than or equal to 100

    • Formula: =SUMIF(B2:B11, "<=100")

    • Result: 250 (100 + 50 + 100)

  6. Sum sales not equal to 200

    • Formula: =SUMIF(B2:B11, "<>200")

    • Result: 1800 (Total sales - 200)

  7. Sum sales for products starting with "A"

    • Formula: =SUMIF(A2:A11, "A*", B2:B11)

    • Result: 500 (Apple sales)

  8. Sum sales for products ending with "e"

    • Formula: =SUMIF(A2:A11, "*e", B2:B11)

    • Result: 500 (Apple sales)

  9. Sum sales for products containing "an"

    • Formula: =SUMIF(A2:A11, "*an*", B2:B11)

    • Result: 550 (Banana sales)

  10. Sum sales for products with exactly 6 letters

    • Formula: =SUMIF(A2:A11, "??????", B2:B11)

    • Result: 550 (Banana sales)

  11. Sum sales for products with sales greater than 150 and less than 400

    • Formula: =SUMIF(B2:B11, ">150") - SUMIF(B2:B11, ">=400")

    • Result: 900 (200 + 250 + 300 + 150)

  12. Sum sales for products except "Orange"

    • Formula: =SUMIF(A2:A11, "<>Orange", B2:B11)

    • Result: 950 (Apple + Banana sales)

  13. Sum sales for products with sales equal to 100

    • Formula: =SUMIF(B2:B11, 100)

    • Result: 200 (100 + 100)

  14. Sum sales for products with sales greater than the average sales

    • Formula: =SUMIF(B2:B11, ">" & AVERAGE(B2:B11))

    • Result: 1300 (200 + 250 + 300 + 400 + 350)

  15. Sum sales for products with sales less than the median sales

    • Formula: =SUMIF(B2:B11, "<" & MEDIAN(B2:B11))

    • Result: 250 (100 + 50 + 100)

  16. Sum sales for products with sales between 100 and 300

    • Formula: =SUMIFS(B2:B11, B2:B11, ">=100", B2:B11, "<=300")

    • Result: 1050 (100 + 200 + 150 + 250 + 100 + 200)

  17. Sum sales for products with sales greater than 200 and product is "Banana"

    • Formula: =SUMIFS(B2:B11, A2:A11, "Banana", B2:B11, ">200")

    • Result: 250 (Only 250 meets the criteria)

  18. Sum sales for products with sales less than 150 and product is "Apple"

    • Formula: =SUMIFS(B2:B11, A2:A11, "Apple", B2:B11, "<150")

    • Result: 150 (100 + 50)

  19. Sum sales for products with sales greater than or equal to 300

    • Formula: =SUMIF(B2:B11, ">=300")

    • Result: 1050 (300 + 400 + 350)

  20. Sum sales for products with sales not equal to 100 or 200

    • Formula: =SUMIFS(B2:B11, B2:B11, "<>100", B2:B11, "<>200")

    • Result: 1450 (Total sales - 100 - 200 - 100 - 200)

on No comments:

Tuesday, 14 January 2025

Excel Dif Function

on No comments:

Thursday, 5 December 2024

collaborating part 1

on No comments:
Subscribe to: Posts (Atom)