Topic 2 - Spreadsheets

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)

Study Unit 4 / 5: Exam – PV (Prev Lesson)
(Next Lesson) Study Unit 4 / 5: Exam – Percentages
Back to Topic 2 – Spreadsheets

No Comments

Comments are closed.