Pinterest Stumbleupon Whatsapp
Ads by Google

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 if you’re working with a really big spreadsheet How To Split a Huge CSV Excel Spreadsheet into Separate Files How To Split a Huge CSV Excel Spreadsheet into Separate Files Read More , it can save a lot of time to use one of these four lookup functions. No matter the size of your Excel document, they’ll be much more efficient.

The VLOOKUP 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 a last name. Here’s the syntax of the function:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])

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.

vlookup-rounding

Let’s take a look at how we might use this. As I have in my articles on boolean operators in Excel Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Logical operators IF, NOT, AND, and OR, can help you get from Excel newbie to power user. We explain the basics of each function and demonstrate how you can use them for maximum results. Read More and advanced counting and adding Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel Counting and adding formulas may appear mundane compared to more advanced Excel formulas. But they can help you save a lot of time when you need to collect information about the data in your spreadsheet. Read More , I’ll be using a spreadsheet that I generated with generatedata.com. It 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:

Ads by Google
=VLOOKUP("Winters", C2:F101, 4, 0)

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:

vlookup-excel

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 Doing Your Taxes? 5 Excel Formulas You Must Know Doing Your Taxes? 5 Excel Formulas You Must Know It's two days before your taxes are due and you don't want to pay another late filing fee. This is the time to leverage the power of Excel to get everything in order. Read More .

Notes on VLOOKUP

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.

The HLOOKUP 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:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])

[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, I’ve created a new spreadsheet with generatedata.com. This one 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:

=HLOOKUP(2013, A1:P51, 24)

(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:

hlookup-excel

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.

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:

=INDEX([array], [row_number], [column_number])
=MATCH([lookup_value], [lookup_array], [match_type])

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.

match-example

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.

index-example

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:

=INDEX(F:F, MATCH("Waters", C:C, 0))

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:

index-match

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 Excel Vs. Access - Can a Spreadsheet Replace a Database? Excel Vs. Access - Can a Spreadsheet Replace a Database? Which tool should you use to manage data? Access and Excel both feature data filtering, collation and querying. We'll show you which one is best suited for your needs. Read More that contained several hundred columns!

Let the Search Begin

Excel has a lot of extremely powerful functions 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things I have always believed that Excel is one of the most powerful software tools out there. It's not just the fact that it's spreadsheet software. No, Microsoft Excel 2013 simply has an awesome collection of... Read More , and the four listed above just scratch the surface. Even with this cursory overview, however, you should be able to save a lot of time when you’re working with large spreadsheets. If you want to see just how much more you can do with INDEX, check out “The Imposing Index” at Excel Hero.

How do you run searches in big spreadsheets? How have you found these lookup functions to be helpful? Share your thoughts and experiences below!

Image Credits: magnifying glass on a spreadsheet Via Shutterstock

  1. Loki
    August 29, 2016 at 2:33 pm

    I have resource name in once column and their hours in next column. I like to sum the total hours which are tagged to a resource. For example, David showed up in column A two times with effort in next column as 2 and 3. I like to get a formula where it shows David worked for 5 hours

    • Dann Albright
      August 31, 2016 at 6:47 pm

      I think you can use this formula: =SUMIF(A:A, "David", B:B). You can replace A:A and B:B with the rows that your data is in. I'm not completely sure that'll get you what you want, but I think it'll work!

  2. Angela V
    August 10, 2016 at 2:17 pm

    I have a question on searching

    I have columns column 1 has reference numbers in it which can be duplicated with difference values in one of the other columns.

    A (ref No) B Assignment
    123456 helpdesk
    123456 PC Support
    123456 Server Support
    123456 Helpdesk
    234567 helpdesk
    234567 helpdesk

    What I want is to be able to count all the ref no.s that only have helpdesk with them. I can get it to count then but it will bring back 1 for the 1set set of numbers and 2 for the 2nd when the answer is really 1 for the 2nd set of numbers only as the 1st set has values that are not helpdesk, as only 1 ref had only helpdesk with it. This is for where support calls have been assigned I only want to count the numbers from A that have only got helpdesk in B, and only 1 count per ref no. I have tried various things but cannot work out what to do with it and is driving me slightly mad.

    • Dann Albright
      August 31, 2016 at 6:50 pm

      I'm not sure how to solve this problem; I tried a couple things, but couldn't find a function that would do the trick. I'd recommend asking the question on reddit.com/r/excel. They're amazing over there.

  3. Stefano Gatto
    August 8, 2016 at 8:14 am

    VLOOKUP is slightly different than FIND because it will find numbers BY VALUE without needing to replicate their format. Try to FIND by value a 1200, while the format has been set to 1'200. FIND won't find it, while VLOOKUP will!

    • Dann Albright
      August 16, 2016 at 2:07 pm

      Thanks for pointing that out; VLOOKUP is really great for things like that!

      • Stefano Gatto
        August 16, 2016 at 4:13 pm

        Yes indeed!

        FIND should find by value, when instructed to do so, not by formatted-form-of-the-value...

        I mean that since VLOOKUP can do it, then why is FIND not doing it?

        Actually I am referring to the VBA FIND() function, which works identically than the worksheet function.

        :-D

  4. Alice
    June 13, 2016 at 11:53 am

    How to return the id B2 in G2= VLOOKUP(652,F:F,?)

  5. saurabh
    February 29, 2016 at 4:06 pm

    Hey can any body help me in the following two things

    1) I want to rename a bunch of folders according to the data in a column in xl file. I have the old names of the folders and the new names to be given in side by side columns. Can anyone point me to the right direction of how to rename them.

    2) I want to match a column cell's content on to a cell in another column and partially match it and give me back the matched value. For example
    Column A Column B Column C
    i have a apple. Mango apple <------ Result
    Banana
    apple

    I want to search the content of column B:B in cell A1 and I want the result to be "apple" or Cell B3 in C1
    Can anybody Help me please.

  6. dan
    January 31, 2016 at 7:10 am

    Is there any convenient way to search for a string within a comment and have EXCEL hi-light the requested string it finds? Some of my EXCEL comments are long and its a nuisance to find the string within the comment that EXCEL is pointing to.

    • Stefano Gatto
      August 16, 2016 at 4:30 pm

      in VBA, you can use a loop to FIND such cells, then SEARCH the substring occurence and the Characters method of the range class to highlight the sequence as you wish.
      However this will destroy all other local formatting in your cell.

      • Dann Albright
        August 21, 2016 at 3:51 pm

        VBA is a good way to go, if you're familiar with it and on a Windows machine. I'm on a Mac, and haven't had a chance to do much with VBA.

  7. Felix
    January 29, 2016 at 3:03 pm

    Hi!

    Great explanation!
    I'm having difficulty to solve a problem I have. I have a column with different products and the date they were produced so I want to have a column that gives the product name including the "no" it is from production. Let's say:

    Model Date of prod. No.of the model being produced
    SG3 2016-05-01 SG3 - 2
    SG3 2015-03-25 SG3 - 1
    IP2 2014-04-05 IP2 - 1
    IP2 2015-04-13 IP2 - 2
    IP4 2013-04-16 IP4 - 1

    So it will look into what model it is and then "sort" the dates for the model and make the oldest date the first one being produced of that particular model.

    I believe there is a way to solve it. Ijust don't see that now..

    • Dann Albright
      February 3, 2016 at 6:30 pm

      Is that a single column, or has it been split into multiple columns? If you can split it into multiple columns, I think sorting / filtering might do the trick.

      • Felix
        February 4, 2016 at 5:08 pm

        It's 3 columns but you basically got 2, the model and the date of prod. and you want to calculate the third column to be "no. of the model beign produced" (or art.nr).

        I tried different ways and solved it. Didn't have to use vlookup at all. :)

        =A1 & " - "& COUNTIFS($A$1:$A$50;A1;$B$1:$B$50;"<"&B1)+1

        So to explain it would be,

        ="model cell name" &" - "& COUNTIFS("Model column";"model cell name";"Date of prod.";"<"&"Date of prod. cell name)+1)

        The "1" to keep adding on the art.nr.

        Works well!

        • Dann Albright
          August 21, 2016 at 3:52 pm

          Awesome! Thanks for sharing the solution you found; I hope someone else finds that useful as well!

  8. Aakash Mehta
    January 21, 2016 at 3:57 pm

    Hi,

    I have two worksheets. One worksheet has 5 to 6 specific names and other worksheet has a list of 5000 names with their details, That is the main worksheet. I want a particular detail for the 5 to 6 name from the main worksheet which contains these names. I tried using index and match function, it gave me the answer. but when I pull it down it does not change name in the function if I want for a different name.

    =INDEX(F:F, MATCH("john", C:C, 0))

    I have names such as

    1 - john - =INDEX(F:F, MATCH("john", C:C, 0))
    2 - mike
    3 - steve

    I use this function and I get detail for john but when I scroll it down it still gives me detail of john.

    • Dann Albright
      February 3, 2016 at 6:29 pm

      So you're saying that the pull-down isn't successfully transferring the name to the next formula? Could you refigure the formula, maybe as =INDEX(F:F, MATCH("[A1]", C:C, 0)), or something like that? That might work better with the pull-down.

  9. David
    December 16, 2015 at 4:37 pm

    Hi, I'm trying to find a formula that would help me solve the following problem:
    Code Country LE#
    123 US 4026
    123 UK 4026
    123 US 3026
    435 CN 1419
    435 CN 1398
    I need to find a formula that can return lowest LE# so if I look up 123 & US it should return with the lowest LE# which is 3026
    Thanks.

    • Dann Albright
      February 3, 2016 at 6:28 pm

      Is it possible to use filtering and sorting to solve this problem? Filter the first column so only 123 shows up, then filter the second column so only US shows up, then sort the final column?

  10. Elie Abeja
    December 5, 2015 at 9:56 am

    In the example above, if you had 100,000 students but only needed to filter out 150 using a list of student IDs,what would you do?

    • Dann Albright
      December 11, 2015 at 9:31 pm

      Hm, that's a tough one, but I think I have an idea. Will do some looking into it and get back to you!

    • Dann Albright
      December 15, 2015 at 2:30 pm

      After doing some looking into this, I think your best best would be use to the fill handle to create 150 different lookup equations. Do you know what I mean. Fill 150 cells with the student IDs, then to the right of the first, enter the equation that will return what you want (using one of the formulas above, depending on what information you're trying to return). Then drag the fill handle down so that you get a corresponding list of the IDs that you're looking for. Does that make sense?

  11. Chris Good
    September 25, 2015 at 4:00 pm

    Hi,
    I'm trying to not just look up one name. I have a spreadsheet of hundreds of names, and another spreadsheet of about 50 names. I am supposed to check and see if any of those names are on the first spreadsheet.

    I usually paste the list of names in the first spreadsheet, highlight everything and do conditional formatting to highlight the duplicates. This is a problem because oftentimes I have multiple people with the same last name, and it doesn't work when the first and last names are in different cells.

    My supervisor said it was easy, just use V lookup. However, from what I can find on V & H Lookup and Index lookup, it seems like you can only search for one name at a time.

    Can anyone explain the best way possible for me to find a group of names on a larger spreadsheet?

    Thanks so much!

    • Dann Albright
      October 5, 2015 at 1:04 pm

      That's an interesting problem. I'm not sure what the best strategy would be here—hopefully an Excel expert will chime in and provide an answer for this one. You could potentially use INDEX / MATCH and label both sets of names with an array name (http://www.engr.mun.ca/~ggeorge/dh/ExcelArrays/ExcelDemo1.html). If you use the array name as the search location, that might solve your problem. Give it a shot and let me know if it works!

    • Emily S.
      December 21, 2015 at 7:18 pm

      You've probably solved this by now, but here's an answer for posterity:

      You need to add in a new column for the vlookup formula, enter the formula in once, and then fill down.

      There are several ways you can resolve the issue of multiple people with the same last name:

      a) Concatenate the names into a single column on each sheet (do this in a new column, and don't get rid of your separate first and last name columns), and use a vlookup formula for the whole name. This will only work if the names are in the same format and there are no typos. If one list has middle initials in the first-name column, for example, you won't get an exact match.

      b) Do a vlookup with just the last name, but add a column next to the "last name" column on your shortlist and use a "countif" formula to determine how many times each last name appears in your long list. Wherever the number is greater than 1, you'll need to check the list manually.

      c) This is a variant of option B, and it's not actually more complicated; I've just explained it in greater detail: Use a vlookup formula on your shortlist to find the first name that corresponds to each last name. Let's say your shortlist has first name in column A and last name in column B, and the names are Barack Obama, Ronald Reagan, and Theodore Roosevelt. In column C, enter in a vlookup formula using the last name from column A as the lookup value, and have the formula return the first name from the long list. If the name is on the list, you'll get the correct first name back: the lookup "Obama" will return "Barack," and "Reagan" will return "Ronald." If a name is not on the list, you will get an "N/A" instead of the name. You may get the wrong name back if there are two people with that last name, though: the lookup "Roosevelt" will return "Franklin." To check for those, in column D, insert and fill down an "if" formula comparing each first name in column C to the corresponding first name in column A: for example, "=IF(C2=A2,"OK","ERR"). Wherever you see "ERR," you'll need to go check the last name on the long list manually.

      • Dann Albright
        January 2, 2016 at 1:20 am

        Thanks for the very detailed solution to this problem! It's obvious that you know what you're talking about, and I always appreciate experts weighing in.

  12. Otto99
    May 11, 2015 at 1:20 pm

    If you are searching a spreadsheet with vlookup and your lookup_value is only a partial string value (e.g. you want to find the first occurance of the string "Jones" ) you can use the wildcard *. So if you use vlookup(*Jones*,f1:r3,10,0) the first value in column f that has the string "Jones" in it will be a match. For example when searching a column of names which may contain first, last and suffix (e.g. Jr, Sr,etc) and you only have the last name. If you search on last name enclosed in * then it will return the first match where that name string occurs. This only works if the partial string is unique as the match will take place for the first occurance.

    • Dann Albright
      May 13, 2015 at 7:29 am

      Thanks for the tip! That's really useful.

  13. Gavin
    May 11, 2015 at 10:47 am

    I've used the HLOOKUP function only once and VLOOKUP a number of times. I also often use the FIND function, both in the worksheet and in Visual Basic.

    • Dann Albright
      May 13, 2015 at 7:28 am

      Yeah, I'm not sure how often HLOOKUP is useful, but it seemed worth including because it's so closely related. I'm sure someone out there finds it useful for something!

Leave a Reply

Your email address will not be published. Required fields are marked *