Vlookup/Hlookup
emma1990
Registered Posts: 34 Epic contributor ๐
Please can someone explain these!!!
0
Comments

Have you watched the video on the AAT website. It's really good0

Please can someone explain these!!!
Hello Emma
What study material do you have for SPSW? Are you going to sit the CBP or provide work based evidence?
Very briefly...
Lookup functions are used to look up relevant data from a table, to use in a calculation.
There are two functions, HLOOKUP, which searches a horizontal table and VLOOKUP, which searches a vertical table.
A Lookup table consists of a selection of bands, or intervals, within which a given value can be found.~ An investment in knowledge always pays the best interest ~Benjamin Franklin0 
Hi Emma
I found when doing SPSW, that You Tube was really helpful, has lots of short clips on how to do things.0 
As no one's had a crack at explaining the actual make up of the formula, I'll have a shot. Not sure if this is what you need. Before starting though, V&h lookups are very useful in the real world, but didn't feature in my SPSW exam.
The difference between the two formula's is just how your data is arranged, v for vertical, h for horizontal.
As for the actual function, they're used when you have some data and want to look up something from another set of data. Good example is if you had a list of car registrations, then perhaps in another place a whole set of car details; registrations, colours etc.
A Vlookup can take your list of registrations and 'fetch' any info you like from the other data that relates to each registration. e.g colour.
The formula is built like this. =vlookup(A1,E1:H300,2,0)
To make some sense of this, always start with the =vlookup to tell excel which formula you want.
The first part of the formula inside the brackets is the cell reference, A1 above. This is the data to lookup; i.e the first registration number we're looking to find the colour for.
The second part is called the array, which means all the cells where excel might find the registration number in cell A1 and some other information we want to fetch. In order for this to work, we need the info we're searching on (registration) and the result we want to return (colour) to be within the cells E1 to H300. Excel will look in the first column of the array for the registration, so column E must contain the registration numbers.
The third section defines which column from our array we want to return as the result, above is set as 2. Excel numbers the columns in the array, starting at the left. So, as our array is E1 to H300, excel will call column E number 1, column F number 2, column G number 3 and so on. If we're trying to fetch the colour of the car, and the colours are stored in column F, we need to put number 2 in the formula.
The final part of the formula tells excel if we want to return an exact match or approximate match. You can use the words "true" or "false", or use the numbers 1 for true and 0 for false. In the real world, false or 0 for an exact match is by far the most useful  approximate matches tend to be sketchy at best.
The end result is excel will look at the registration in A1, then find a match for it anywhere in column E and return the colour for that registration from column F.
There's quite a bit to digest if you're not used to using these formula's often. Best advice is practise; it often looks like gobbeldygook when explained in writing, but try a few examples and it'll click.
It's a cracking function once you master it.
Good luck :thumbup:0 
Nice explanation nightshade!
I would add Vlookup is massively dependant on Unique ID's so it is looking up a unique ID (car reg in A1) against the car reg in the array.
However the limiting factor is that the car reg has to be unique (which it should be), but if say someone had a fake number plate and two cars with identical reg were in the array, the Vlookup would "return" the colour for the first car.
Always make sure your using a unique ID. by that the ID will only feature in your array once.
For an array containing names say, there could be several daves, so considert this when using Vlookups0 
wow thank you everyone for your replies!!
I have got the concept of it now and am slowly getting the hang of it!
Thank you0
Categories
 All Categories
 1.2K Books to buy and sell
 2.3K General discussion
 12.5K For AAT students
 314 NEW! Qualifications 2022
 159 General Qualifications 2022 discussion
 11 AAT Level 2 Certificate in Accounting
 56 AAT Level 3 Diploma in Accounting
 85 AAT Level 4 Diploma in Professional Accounting
 8.8K For accounting professionals
 23 coronavirus (Covid19)
 272 VAT
 92 Software
 273 Tax
 135 Bookkeeping
 7.2K General accounting discussion
 200 AAT member discussion
 3.8K For everyone
 38 AAT news and announcements
 345 Feedback for AAT
 2.8K Chat and offtopic discussion
 581 Job postings
 16 Who can benefit from AAT?
 36 Where can AAT take me?
 42 Getting started with AAT
 26 Finding an AAT training provider
 48 Distance learning and other ways to study AAT
 25 Apprenticeships
 66 AAT membership