Scribd
Upload
3 views2 pages

Program 3 - DA LAB

The document provides instructions on using various Excel functions including VLOOKUP, HLOOKUP, XLOOKUP, COUNT, and COUNTA with specific datasets. It includes examples for finding product names, prices, categories, sales, and profits using these functions. Additionally, it demonstrates how to handle cases where a product is not found using error handling techniques.

Uploaded by

nitheshnithesh84584
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views2 pages

Program 3 - DA LAB

The document provides instructions on using various Excel functions including VLOOKUP, HLOOKUP, XLOOKUP, COUNT, and COUNTA with specific datasets. It includes examples for finding product names, prices, categories, sales, and profits using these functions. Additionally, it demonstrates how to handle cases where a product is not found using error handling techniques.

Uploaded by

nitheshnithesh84584
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

3.

To demonstrate the use of VLOOKUP, HLOOKUP, XLOOKUP, COUNT and COUNTA


functions.

Dataset for VLOOKUP and XLOOKUP

Enter the following data in cells A1:D6.

Product ID Product Name Category Price


P101 Laptop Electronics 55000
P102 Mouse Accessories 800
P103 Keyboard Accessories 1500
P104 Printer Electronics 12000
P105 Chair Furniture 4500

1. VLOOKUP Function

To find the product name for product ID P103:

=VLOOKUP("P103",A2:D6,2,FALSE)

Output
Keyboard

To find the price of product ID P104:

=VLOOKUP("P104",A2:D6,4,FALSE)

Output
12000

2. XLOOKUP Function

To find the category of product ID P105:

=XLOOKUP("P105",A2:A6,C2:C6) (works in excel 2021 and above)

Instead use this

=INDEX(C2:C6, MATCH("P105", A2:A6, 0))

Output
Furniture

To return a message when the product is not found:

=XLOOKUP("P110",A2:A6,B2:B6,"Product not found") (works in excel 2021


and above)
Instead use this

=IFERROR(INDEX(B2:B6, MATCH("P110", A2:A6, 0)), "Product not found")

Output
Product not found

Dataset for HLOOKUP

Enter the following data in cells A9:E11.

Field Q1 Q2 Q3 Q4
Sales 25000 32000 28000 40000
Profit 5000 7000 6000 9000

3. HLOOKUP Function

To find the sales value for Q3: =HLOOKUP("Q3",A9:E11,2,FALSE)

Output
28000

To find the profit for Q4:

=HLOOKUP("Q4",A9:E11,3,FALSE)

Output
9000

4. COUNT Function

COUNT counts only cells containing numbers.

=COUNT(D2:D6)

Output
5

5. COUNTA Function

COUNTA counts all non-empty cells. =COUNTA(A2:D6)

Output
20

You might also like