98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Vlookup
Vlookup
Resolved · Low Priority · Version Standard
Leigh-anne has attended:
Excel Advanced course
Access Introduction course
Vlookup
doing a vlookup from a number of sheets
RE: vlookup
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
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Recently used file listUnder the File menu, you may find a list of files at the bottom of the menu. These files represent the most recently used Excel spreadsheets. This file list provides a quick way for you to access your files. |