Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.I've used the VLOOKUP function since the cows last came home. I can't imagine my Excel life without it. It's sublime, and while a relatively simple function, it caters for so many needs. (More on the VLOOKUP function here.)
It takes on the following form:
=VLOOKUP (needle, haystack, column reference, TRUE/FALSE)
The needle is the thing you're looking for. The haystack is the range in which you're looking for it. The column reference is the column number that you want to return. And the TRUE/FALSE at the end determines whether to only find exact matches (FALSE, or 0); or look for inexact matches (TRUE, or 1).
Below is Microsoft's description of this last variable:
If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
To this day, in 20+ years of using the software, I've never set this last variable to TRUE. Not once. For some reason, I've always been nervous about inexact matches, and the need to find near misses has never arisen for me.
Or it hadn't, until last week.
My friend Steve emailed me. He had a spreadsheet that told him which person had bought which ranges of raffle tickets. (So there were three columns: name, lowest numbered ticket bought, highest numbered ticket bought.) He needed the ability to type in a number of a drawn raffle ticket, and for it to pop up with the name of the winning ticket holder.
At first, it stumped me. But a couple of minutes in, I realised that the TRUE setting would be perfect here. All I had to do was put the people's names to the right of the range, and I could look up the winning ticket number against the column containing the lowest numbered ticket bought, and hey presto!
If Joe has bought tickets 1 through 100, then by setting the variable to TRUE, a lookup of the number 86 will bring back Joe's row, as 1 will be the next lowest number it finds. (Setting it to FALSE would bring back #N/A, as there wouldn't be an exact match.)
It's lovely when something that initially troubles your mind can be solved so simply.
hi, i am at the moment using manuel method to input data into excel. we dothis on a monthly basis, we need to extract the product code and description in and then manually input the price but i was hoping to make a master sheet with all the product codes anf prices and then every month just put the product code in amd the price read from the master,i have worked with vlook ups before but am not sure how they work exactly...im hoping this is somethin u cam help me with
Hi, I am with a database of GPS coordinates. We recently took new more accurate GPS points. We are linking data from where the old GPS point was taken to the new points. An example of this would be the old point is: 30.53699 and the new point is: 30.536995. I am using VLOOKUP but I can't make this example work. Any suggestions to solve this are welcome.