doing a vlookup from a number of sheets

Dear Leigh-anne

Thank you for attending the Excel course. I hope you found the course enjoyable as well as useful.

VLookup can be performed within the same sheet as well as various different sheets.

VLookup is basically a function that can help you to retrieve data from a table that may have lots of information. Please note that you can only use Vlookup if the column headings in the table are arranged vertically. Normally people have the data arranged in this format. If the main headings are arranged in rows then you have you use the HLookup.

The syntax of a VLOOKUP is :

=VLOOKUP(Lookup value, Table Array, Column Index Number, Range Lookup)

If a cell has a function such as:

=VLOOKUP(A3,A5:F70,2,FALSE)


=Vlookup( is the name of the function

A3 is the cell which other cells on the right will refer to in order to display the result. Therefore, this will be our Lookup value.


A5:F70 is known as Table Array. This is the big table that Excel will be looking into to find the result.

2 is the column Index number it will extract from the Table array.


False is the Range Lookup and ensures that you get an Exact match rather than an approximate match. This means that in Cell A3 if you entered E0015 by mistake then Excel will just give you a #N/A error because it cannot find that entry in the table array.

If you leave Range lookup Blank or enter True then it will give you an approximate match. This means that if you did type in E0003 by mistake it will still give you the Result of E003 which will be wrong in our case. Range Lookup is best left blank if you are trying to get e.g. Commission rate of sales people or Discount figure based of the amount of sales made.

NB: It is important that you sort the column e.g. the sales data is arranged in ascending order if you are looking for the Approximate or close match!

Once you get the result. This can be repeated for rest of the field.

The above mentioned explanation is suitable if the all the data that you are trying to lookup is on the same sheet.

To use the data coming from different sheets simply select the relevant sheet for the table Array and refer the column index number on the new table array!

I hope this helps.

If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!

Kindest Regards


Rajeev Rawat
MOS Master Instructor 2000 and 2003