How to use LOOKUP Function in Excel

Most of the time, finding a cell in an Excel spreadsheet is pretty easy; if you can’t just scan through the rows and columns for it, you can use CTRL+F to search for it. But it could be difficult and time-consuming if you’re working with a really big spreadsheet. Today I will show you, How to find something in excel with Lookup Function.

How to use V LOOKUP Function:

This function allows you to specify a column and a value, and will return a value from the corresponding row of a different column (if that doesn’t make sense, it’ll become clear in a moment). Two examples where you might do this are looking up an employee’s last name by their employee number or finding a phone number by specifying the last name. Here’s the syntax of the function:

The (lookup_value) is the piece of information that you already have; for example, if you need to know what state a city is in, it would be the name of the city. (table_array) lets you specify the cells in which the function will look for the lookup and return values. When selecting your range, be sure that the first column included in your array is the one that will include your lookup value! This is crucial. (col_index_num) is the number of the column that contains the return value.

(range_lookup) is an optional argument, and takes 1 or 0. If you enter 1 or omit this argument, the function looks for the value you entered or the next-lowest number. So in the image below, a VLOOKUP looking for an SAT score of 652 will return 646, as it’s the closest number in the list that’s less than 652, and (range_lookup) defaults to 1.

My spreadsheet contains ID numbers, first and last names, city, state, and SAT scores. Let’s say I want to find the SAT score of a person with the last name “Winters.” VLOOKUP makes it easy. Here’s the formula we’d use:

Because the SAT scores are the fourth column over from the last name column, I’ve used 4 for the column index argument. Note that when you’re looking for text, setting (range_lookup) to 0 is a good idea; without it, you can get bad results. Here’s what Excel gives us:

It returned 651, the SAT score belonging to the student named Kennedy Winters, who is in row 92 (displayed in the inset above). It would’ve taken a lot longer to scroll through looking for the name than it did to quickly type out the syntax!

VLOOKUP can also be quite useful if you’re using Excel to do your taxes.

Notes:

A few things are good to remember when you’re using VLOOKUP. First, as I mentioned previously, make sure that the first column in your range is the one that includes your lookup value. If it’s not in the first column, the function will return incorrect results. If your columns are well organized, this shouldn’t be a problem.

The second thing to keep in mind is that VLOOKUP will only ever return one value. If we’d used “Georgia” as the lookup value, it would have returned the score of the first student from Georgia, and given no indication that there are in fact two students from Georgia.

ALSO READ:

• HOW TO USE YANDEX MAIL KEYBOARD SHORTCUTS
• HOW TO RECALL MAIL IN OUTLOOK
• HOW TO REDUCE SIZE OF AN EXCEL FILE

The H LOOKUP Function

Where VLOOKUP finds corresponding values in another column, HLOOKUP finds corresponding values in a different row. Because it’s usually easiest to scan through column headings until you find the right one and use a filter to find what you’re looking for, HLOOKUP is best used when you have really big spreadsheets or you’re working with values that are organized by time. Here’s the syntax:

(lookup_value), again, is the value that you know and want to find a corresponding value for. (table_array) is the cells in which you want to search. (row_index_num) specifies the row that the return value will come from. And (range_lookup) is the same as above; leave it blank to get the nearest value when possible, or enter 0 to only look for exact matches.

For this example, This spreadsheet contains a row for each state, along with an SAT score (we’ll say it’s the average score for the state) in the years 2000–2014. We’ll use HLOOKUP to find the average score in Minnesota in 2013. Here’s how we’ll do it:

(Note that 2013 is not in quotes because it’s a number, and not a string; also, the 24 comes from Minnesota being in the 24th row.) As you can see in the image below, the score is returned:

Minnesotans averaged a score of 1014 in 2013.

Notes on HLOOKUP

As with VLOOKUP, the lookup value needs to be in the first row of your table array; this is rarely an issue with HLOOKUP, as you’ll usually be using a column title for a lookup value. HLOOKUP also only returns a single value.

ALSO READ:

• HOW TO SET UP AND USE SKYPE TRANSLATOR
• HOW TO RECOVER FILES FROM A CORRUPT USB DRIVE
• HOW TO SEE WHICH APPLICATIONS ARE DRAINING YOUR BATTERY ON WINDOWS 10

The INDEX and MATCH Functions

INDEX and MATCH are two different functions, but when they’re used together they can make searching a large spreadsheet a lot faster. Both functions have drawbacks, but by combining them we’ll build on the strengths of both. First, though, the syntax:

In INDEX, (array) is the array in which you’ll be searching. (row_number) and (column_number) can be used to narrow your search; we’ll take a look at that in a moment.

MATCH’s (lookup_value) is a search term that can be a string or a number; (lookup_array) is the array in which Excel will look for the search term. (match_type) is an optional argument that can be 1, 0, or -1; 1 will return the largest value that is smaller than or equal to your search term; 0 will only return your exact term; and -1 will return the smallest value that is greater than or equal to your search term.

It might not be clear how we’re going to use these two functions together, so I’ll lay it out here. MATCH takes a search term and returns a cell reference. In the image below, you can see that in a search for the value 646 in column F, MATCH returns 4.

INDEX, on the other hand, does the opposite: it takes a cell reference and returns the value in it. You can see here that, when told to return the sixth cell of the City column, INDEX returns “Anchorage,” the value from row 6.

What we’re going to do is combine the two so that MATCH returns a cell reference and INDEX uses that reference to look up the value in a cell. Let’s say you remember that there was a student whose last name was Waters, and you want to see what this student’s score was. Here’s the formula we’ll use:

You’ll notice that the match type is set to 0 here; when you’re looking for a string, that’s what you’ll want to use. Here’s what we get when we run that function:

As you can see from the inset, Owen Waters scored 1720, the number that appears when we run the function. This may not seem all that useful when you can just look a few columns over, but imagine how much time you’d save if you had to do it 50 times on a large database spreadsheet that contained several hundred columns!

It should save you a lot of time when you’re working with large spreadsheets.

Not only with your Excel, you can get help with any technical issue with our computer just by creating a FREE support ticket with our technical experts.

Source: Makeuseof