Vlookup/Hlookup

emma1990

Please can someone explain these!!!

Mollypod88

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

Jo Clark

emma1990 wrote: »

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.

janwal

Hi Emma

I found when doing SPSW, that You Tube was really helpful, has lots of short clips on how to do things.

nightshade

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:

ExcelAnt

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 Vlookups

emma1990

wow thank you everyone for your replies!!

I have got the concept of it now and am slowly getting the hang of it!

Thank you