## Study Unit 4 / 5: Exam – VLOOKUP

EXPLAIN THE VLOOKUP FUNCTION IN EXCEL.

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

WHAT IS THE SYNTAX?

=VLOOKUP(ITEMTOFIND,RANGETOLOOKIN,COLUMNTOPICKFROM,SORTEDORUNSORTED)

The itemtofind is a single item specified by the user.

• lookup_value: the value to be found (text string / value / cell ref)

The rangetolookin is the range of data with the row headings at the left hand side.

• table_array: where the value must be looked for (range / range name)

The columntopickfrom is how far across the table the function should look to pick from.

• col_index_num: the number of the column that must be returned

The sorted/unsorted is whether the column headings are sorted.

• range_lookup: true, false

• true for yes = find the closest match
• false for no = find an exact match

YOU MUST ENTER THREE REQUIRED ARGUMENTS:

1. – WRITE THE CELL ADDRESS CONTAINING THE VALUE TO GET ANOTHER VALUE
2. – WRITE THE LIST’S CELL RANGE
3. – WRITE THE COLUMN THAT CONTAINS THE VALUE YOU WANT TO RETRIEVE
4. – WRITE THE RANGE LOOKUP AS EITHER TRUE OR FALSE

EXAMPLE:

1. INPUT THE CELL ADDRESS CONTAINING THE VALUE TO GET ANOTHER VALUE

THE ITEMTOFIND IS A SINGLE VALUE CAN BE A TEXT OR A CELL REFERENCE

=VLOOKUP(30,rangetolookin,columntopickfrom,true/false)

1. INPUT THE LIST’S CELL RANGE

THE RANGETOLOOKIN IS THE AREA (RANGE) THAT HAS THE MATCHING ANSWER

=VLOOKUP(30,\$A2:\$C5,columntopickfrom,true/false)

1. INPUT THE COLUMN NUMBER THAT CONTAINS THE VALUE YOU WANT

IN THIS RANGE 3 COLUMNS (A&B&C) WERE SELECTED, SO IT’S NUMBER 3 THAT HAS THE ANSWER WE NEED

=VLOOKUP(30,\$A2:\$C5,3,true/false)

1. INPUT THE RANGE LOOKUP IS EITHER TRUE OR FALSE

TRUE FOR YES = FIND THE CLOSEST MATCH

FALSE FOR NO = FIND EXACT MATCH

=VLOOKUP(30,\$A2:\$C5,3,TRUE)