Use of Formulas – MATCH & VLOOKUP with MATCH

Coffee Price List

Use the following table to practise the MATCH and VLOOKUP with MATCH functions.

Coffee	SMALL	MEDIUM	LARGE
Cafe Latte	₹2.75	₹3.50	₹4.25
Cappuccino	₹2.95	₹3.65	₹4.40
Caramel Macchiato	₹3.25	₹3.90	₹4.50
Cafe Mocha	₹3.00	₹3.75	₹4.35
White Chocolate Mocha	₹3.50	₹4.20	₹4.75
Americano	₹2.00	₹2.50	₹3.00
Cinnamon Latte	₹3.40	₹4.00	₹4.60
Espresso	₹2.25	₹2.75	₹3.25
Iced Coffee	₹2.50	₹3.25	₹3.95
Cold Brew	₹3.25	₹3.75	₹4.50

Questions

Q.1

Find the column number of SMALL using the MATCH function.

Use of: MATCH

Q.2

Find the column number of MEDIUM using the MATCH function.

Use of: MATCH

Q.3

Find the column number of LARGE using the MATCH function.

Use of: MATCH

Q.4

Find the price of Cafe Mocha – MEDIUM using VLOOKUP with MATCH.

Use of: VLOOKUP + MATCH

Q.5

Find the price of Cappuccino – LARGE using VLOOKUP with MATCH.

Use of: VLOOKUP + MATCH

Q.6

Find the price of Americano – SMALL using VLOOKUP with MATCH.

Use of: VLOOKUP + MATCH

Q.7

Find the price of White Chocolate Mocha – LARGE using VLOOKUP with MATCH.

Use of: VLOOKUP + MATCH

Q.8

Find the price of Cinnamon Latte – MEDIUM using VLOOKUP with MATCH.

Use of: VLOOKUP + MATCH

Q.9

Find the price of Cold Brew – SMALL using VLOOKUP with MATCH.

Use of: VLOOKUP + MATCH

Q.10

Find the price of Caramel Macchiato – LARGE using VLOOKUP with MATCH.

Use of: VLOOKUP + MATCH

Functions to Practise

Function	Purpose
MATCH	Finds the position/column number of a value
VLOOKUP	Searches for a value in the first column of a table
VLOOKUP + MATCH	Dynamically finds the required column and returns the corresponding value

Basic Syntax

MATCH:

=MATCH(lookup_value,lookup_array,0)

VLOOKUP with MATCH:

=VLOOKUP(lookup_value,table_array,MATCH(column_name,header_row,0),FALSE)