vlookup(), match() and offset() – explained in plain english [spreadcheats]

Posted on November 19th, 2008 in Featured , Learn Excel - 198 comments

VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, OFFSET and MATCH worksheet formulas can transform you from normal excel user to a data processing beast.

Today, lets understand how to use these formulas better.

What is the syntax for Match, Vlookup and Offset?

Here is the syntax for these three very powerful functions in plain English:

vlookup-match-offset-formulas-help-syntax

What are vlookup () and match () ?

VLOOKUP and MATCH are your way of asking excel to find a needle in haystack. Imagine you have all your customer contact information in one sheet in the range A1:D5000 in the format phone number, name, city and date of birth. Now you need to find out which customer has the phone number “936-174-5910″. How do you do it?

You guessed it right, you use VLOOKUP and summon excel to do the search and return with customer name.

While VLOOKUP is used to fetch value a based on what you are looking for, MATCH is used to fetch the position of the value you are looking for.

See this illustration to understand :

difference-between-vlookup-match-excel-formulas

What does VLOOKUP really do?

Imagine you have a list of data like this:

Sample Data - VLOOKUP Excel Formula

Now, how do you answer the question – “How many sales did Jimmy make?

Yes, your guess is right. VLOOKUP is one of the formulas you can use to answer questions like this.

VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns.

So, in our case, we need VLOOKUP to search for Jimmy and return the amount of sales he made from column 3.

VLOOKUP Syntax & Examples:

The syntax of VLOOKUP is simple:

=VLOOKUP( this value, your data table, column number, optional is your table sorted?)

Here is an example to get you started:

VLOOKUP Excel Formula - Syntax and Examples

VLOOKUP Examples & Homework

I have made a small excel file detailing 4 VLOOKUP formula examples. The file also contains some home work so that you can practice this formula.

Download VLOOKUP Example Workbook

So what is Offset() then?

OFFSET is your way of telling excel to fetch a portion from large range of values. You can compare OFFSET to what you see from your car window while driving. As your car moves, you see different things from the window.

OFFSET returns a reference to the portion of a large range you have supplied based on 5 parameters. For eg. OFFSET (A1, 3,4, 5,6) would return 5×6 cell range from E4 (A+4 columns, 1+3 rows = E4) thus: E4: J9

See this illustration to understand how OFFSET spreadsheet formula works:

how-offset-excel-formula-works

 

Related: Learn more about OFFSET Formula.

So how are Offset() and Match() linked to each other?

Since MATCH returns the position of the item you are looking for in a list, you can then use this position in OFFSET to fetch values surrounding the searched value.

Related: Using INDEX instead of OFFSET.

Finally

Remember, both VLOOKUP and MATCH throw a fail error of #value! if the value you are looking for is not there. Also, OFFSET returns a range so make sure you pass the value to another function like SUM that accepts ranges.

Just use them with some dummy data, play around with arguments and see how you can say “oh yeah, I can do that in few minutes” to your boss next time.

Want to Learn More Formulas? Get my VLOOKUP book

If you want to learn VLOOKUP and other Excel lookup functions, then consider getting my VLOOKUP book.

The VLOOKUP Book - Definitive guide to Excel lookup functions & tricks
Comprehensive and easy to understand

This is a book for everyone who uses Vlookup. Most of us think… Oh.. I already know the function. But this book will open your eyes to some brilliant techniques. – By Dr. Nitin Paranjape
Solid introduction to lookup functions

This books does a wonderful job of taking each of the lookup functions available in Excel, breaking them down to a simple, easy-to-understand level. – by Lucas Moraga



Get your copy

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

198 Responses to “vlookup(), match() and offset() – explained in plain english [spreadcheats]”

  1. Ketan says:

    @Chandoo : Also, add HLOOKUP here . VLOOKUP : vertical look up i.e. work in rows.. where HLOOKUP : horizintal look up i.e. work in Columns…

    These are very useful to fetch the data from other data base. Also, very useful in auditing and linking the data to take a business decision.

  2. Sam Krysiak says:

    I use these all the time – they’re *that* useful when trying to find the proverbial needle in a haystack.

    Well done for gathering these functions into a simple tutorial – now I really can say to my boss “Oh yeah, I can do that in a few minutes” ;) Keep up the great work Chandoo

  3. sam says:

    Dont use VLOOKUP or HLOOKUP, instead use INDEX/MATCH

    Name, Amt, Region

    Index(Region,Match(WhatToMatch, Name, 0))

    Sam

    • Louie says:

      Index and Match is usefull, but it gets complicated too quickly. Its almost easier to simply move the key in a large data table all the way to left and just do a vlookup.

      Sometimes, if what I’m searching for is a moving target across the page, meaning the column I’m returning from will change from day to day, I will use a and Index & Match function, or even a vlookup/match combo.

  4. Dustin says:

    I use the vlookup all the time. I find a lot of times I need to use it when I know the item might not be in the list. As mentioned above, if the item isn’t there, you get a “#NA”, which keeps you from summing or doing anything else useful with the collected information you’ve built.

    So I use the following combination of formulas:
    IF(ISERROR(VLOOKUP(what, where, return_what, [mylist is sorted])), 0, VLOOKUP(what, where, return_what,[my list is sorted]))

    Based on the write-up above, that breaks down like this:
    IF(try this, if it works do this, if it doesn’t work do this)
    ISERROR(tell me if there’s an error here)
    VLOOKUP(as above)

    So basically, it runs the vlookup to see if it finds anything. If it doesn’t find anything it returns an error. If it returns an error the ISERROR is true, so it puts a zero in the cell. If the first VLOOKUP returns a value, it runs the VLOOKUP again and puts the value in the cell.

    You can certainly replace the zero with “” to put nothing in the cell or just about anything else. I’ve put in strings like “No” that I use to sort the info later, etc.

    I’m sure there’s more efficient ways to do this (maybe using match to verify the item is there), but this has worked very well for me for about 7 or 8 years without fail. I’ve used it in some very large project planning workbooks, commision calculations for sales, as well as a couple of different financial models and never had a problem with it slowing down Excel.

    • Ayman says:

      =IFERROR(VLOOKUP(what, where, return_what, [mylist is sorted])), 0) returns the same

      • Etienne says:

        Great Stuff!!! I think these comments just saved me hours of resorting and deleting! I always used to make a new coloumn at the beginning called “sort”, number it 1,2,3,etc so I could then sort the entire sheet by the vlookup vlaues and delete the #’s. I’m pretty happy to stop doing that now :)

        I love your way of explaining things by the way Chandoo. Thanx

  5. Chandoo says:

    @Ketan: Thank you so much for suggesting HLOOKUP(). I deliberately left out hlookup(), index() few other formulas as I wanted to keep this post short and digestible. I agree that hlookup is as useful as vlookup and you should choose these functions based on the situation.

    @Sam1 : thank you so much for the words.

    @Sam2:I guess there are multiple ways to solve same problem and we should choose what is best for us. I feel comfortable using vlookup, you might prefer index and match combo.

    @Dustin: Awesome stuff… It is so nice that you have explained basic error handling to everyone :)

    I use error handling to ensure that those ugly #xxx! messages are gone. Another thing I try often is to use conditional formatting to de-emphasize or highlight errors so that I can nail them down. This is useful when I am cleaning data as there is no place for #values! here..

    check this out: http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

  6. sam says:

    Both VLookup and Hlookup have the following limitations

    1. Vlookup can look only to the right not to the left, Hlookup can look down not up
    Index/Match does not have these limitation

    2. Vlookup can only find the closest match on the lower side not on the higher side
    Match has 3 type – Exact(Unsorted) Next Lower(Ascending), Next Higher(Descending)

    3. Vlookup can only look based on a single criteria, Index/Match – array entered can lookup on multiple conditions

    4. A Vlookup formula cant be dragged across unless you have a helper row or the Column function nested inside it

    • Louie says:

      1. V&H lookup is easier to both type and pick from the forumula helper than the combination of Index and Match (Not as many fields to fill out). Plus, you can just move your column index to the far left or far top, or simply add filters and sort your data and they will work fine.

      Sometimes its great to have a huge brain that can think up all the combinations of rows columns you could be looking for, but sometimes you just need something quick and dirty.

      2. I agree with this point

      3. I agree with this point as well

      For 4 this is not true. At least in that respect its no different than Index/Match as you can also do a Vlookup/Match.

      But it is still limited by the other the other factors you mentioned above.

  7. Kekojones says:

    Love your site Chandoo. Came here from Lifehacker and Im just amazed how great is the material. Expert, medium or basic users can learn many thing from this site.

    Best thing is that you don’t focused on VBA so one can explain this techniques to non programmer guys to improve productivy and the quality of work.

    Keep the good work!!! Your site is excelent.

    P.S. Could you post some excel color palletes or show us a way to mimic the colors combos used by Excel 2007.

  8. Phil Gilliam says:

    Great stuff…I use vlookup, hlookup extensively but while I know quite a bit of the tips and hints you provide, I still love getting your emails. I almost always learn something from you or one of your readers posts PLEASE don’t ever let my name be deleted from you send list!

    By the way, thanks Dustin, I had used your formula before but had forgotten how I did it and just yesterday I was trying to remember how to eliminate the #NA error. You must be psychic and you must have known I needed your help!

  9. Chandoo says:

    @Sam: Agree :)

    There are various limitations to vlookup/hlookup. Put in another way, MS has designed these functions with particular need in mind. A one formula solution for lists organized based on one key column on the left and several attribute columns on the right.

    I am glad you started this discussion as there are sizeable chunk of readers looking for more powerful options to search / lookup of lists. I may write another post in the coming weeks to discuss using index, match based solution as well.

    @Phil: It is certainly a great feeling to wake up and read a comment like this. I feel very happy and thrilled :)

    someone said, “the best blog posts start where comments begin” and I totally agree with it. There is so much I have learned through this blog and I owe it to commenters all over the world.

  10. Thanks a lot

    this so easy and nice illustration for there very useful function

    keep up the good work

  11. [...] vlookup(), match() and offset() – explained in plain english [spreadcheats] (11 comments) [...]

  12. hi,

    I am new to VBA, looking for great stuff’s in VBA.

    this site looks fresh in ideas. like to some advices on VBA

    bon,

    Antoi.

  13. Kartikeya says:

    Great Article!!!
    I have a stupid question. If the data in my array is not sorted, how does the match function behaves if we put the “how?”= 1 and “how?”=-1. Does it sorts the data in ascending/descending order and then displays the position? As we use =INDEX(A:A,MATCH(9.99999999999999E307,A:A, 1)) to find the last value in a column.

    Thanks for the help.

  14. Chandoo says:

    @Anthony, Social Wonders: thank you. Sure I will cover few VBA ideas in the coming weeks. As a rule we try to avoid VBA for its complexity to understand and implement.

    @Kartikeya: Usually match assumes your data is sorted and returns the first value less than or more than the match value you are looking for. Thus the result will be wrong.

    Btw, your formula =INDEX(A:A,MATCH(9.99999999999999E307,A:A, 1)) returns the last value in a column because 9.99999999999999 * 10^307 is technically the largest value possible in excel. So Match returns last value in the range A:A. You can replace 9.9999… with max(A:A) and the formula still works (although does one additional calculation)

    Also, if you want to sort numbers use small() and large() functions. See more here: http://chandoo.org/wp/2008/08/13/15-microsoft-excel-formulas/

  15. Rob says:

    VLOOKUP works great, but I have another challenge.

    I need to match 2 cells by looking at a three letter code.
    Example; I have in my main sheet the code Pabc123 and want to match this with code abcx1000-1 in another sheet.

    So I need to match the abc from the first code with the abc of the second code.

    My data has multipe codes so I need to use wildcards for this.

    So I need a formula which does not hus the lettercode abc, but instead looks for matching the 2nd, 3rd and 4th positions of the first code with the first 3 potitions of the second code.

    So does someone know if this is possible?

    • Rahul says:

      HI Rob,
      As an example I have put the letters abc for the purpose of lookup in cols B and C and tried the formula. You edit the fomula and use it inyour look-up or one can wild card abc and use it instead using the wild character at the end of letters abc e.g.abc*

      =IF(MID(B13,1,3)=”abc”,VLOOKUP(B13,B13:C21,2,0),)

  16. [...] I used Excel formulas OFFSET() and ROW() and COLUMN() to rearrange the data in a tabular format (the original format is a [...]

  17. [...] are other ways of looking up multiple criterias in Excel such as arrays. This is by far the easiest way of getting [...]

  18. Jonathanlee642 says:

    Hi, I am a new user and I am very happy to read all the comments posted here. I have a basic question that needs help from you please.

    I have 2 sets of accounting data to check, one from the bank statement, and the other is my internal company financial statement. Both sets use different different formats to present the financial data. When doing bank recon, I need to check a specific amount that appeared in the bank statement, its corresponding location (ie same amount etc) that appear in our internal company financial statement. May I know what is the most most efficient way to do this? Currently, I do manual check. I was told that I can do vlook or match, but I do not know how to do it. I am not good that excel functions. I am just a basic beginner excel user. Thanks.

  19. QualityGuru says:

    I have an issue wondering if you can help, I want a range of cells in % value to be matched against the customer name in another range of cells then to put this value in a top 4 column for instance

    Can post the file if it helps

    • Chandoo says:

      @QualityGuru: Welcome to PHD and thanks for asking your question.

      You can use MATCH() to match a value in a range, as showed above, it returns, the location # of the match in the range. You can then use this to get the corresponding element in the other range, using OFFSET().

      Does this answer your question ? If not, please post another comment detailing where I misunderstood. I will try to answer it.

  20. [...] you ever used Match() or other lookup functions and felt that the need for matching multiple items in a range? Well, you can do that using UDF [...]

  21. Ash says:

    for ROB –> if you are sure that the information you need is always the 2nd, 3rd and 4rd positions within the code, you can put on a column on the side of the code column a MID formula referring to it, such as
    =MID(cell you refer to, 2, 3)
    where 2 = the 1st position you recuperate and 3 = the total number of positions from there that you recuperate. This will isolate the 3 positions you want.
    Same thing for getting the 1st 3 positions of the second code – or for this one use the formula LEFT(cell you refer to, 3) for same result.
    Then you can do Vlookup formula on these intermediary columns which contain the 3 positions sample. It adds columns but saves time!

  22. Mark says:

    Hi,

    Can someone answer a question about the MATCH() command, for me, please?

    I have been trying to replace various vlookup() commands with index()/match() combinations. These work fine until I try to reference an array which is on a different workbook. Then I get a #N/A error.

    Can match() handle accessing data from a remote workbook or am I limited to local data only?

    Regards,

    Mark

  23. Robert says:

    Mark,

    as far as I know, MATCH is working fine with references to other (even closed) workbooks, as long as the array is a reference to another other workbook. But MATCH does not work if the search value is a reference to another workbook.

  24. Chandoo says:

    @Mark: as Robert suggested, the match should work when you refer to other workbooks. Often when the other workbook is closed, you may need to change the formulas to reflect the complete path (this is usually done automatically by excel).

    Long back I used this and a User Defined Function to consolidate a bunch of reports. I had to use the UDF since one of the functions wouldnt work with remote workbooks (I dont remember which one it is, but I guess it is indirect() …)

    Anyways, if you find a workaround for this, please feel free to share with us all and you will get a donut. That is our policy :)

  25. [...] is one of the frequent errors you see while using vlookup formula. The N/A error is shown when some data is missing, or inappropriate arguments are passed to the [...]

  26. Mark says:

    Thanks, guys. I found the problem with the MATCH() referencing an external workbook. Let’s just say the keys were too small for the fingers punching them. ;)

    Now all I need to do is work out if I can access these functions programatically from withing VBA. Then I can make prettier sheet function calls rather than have calls that are about six or eight lines (screen width in length).

    I’ll let you know how I get on.

  27. [...] We can do that using excel formulas AVERAGE, COUNT and OFFSET [...]

  28. Srinivas Vayuvegula says:

    Dear Excel Guru.
    Good Day.
    In excel spead sheet i have data in sheets. The data of salaries of 50 employees, for every month one one sheet is prepared. The names basic sal, da, hra, gross & total ded and net total are the same in every sheet. My question is How can i sum up in the summary in 13th sheet (for one month one sheet so 12 sheet and 13 is summary) if i want sum up in summary sheet. Function has to verify the name and add from all the sheets ie basic put value in 13th sheet. Kindly help me how to do this. thanks in advance

  29. Dustin says:

    Srinivas,

    I honestly can’t tell exactly what you’re trying to do. While we talked about match/vlookup/etc above, it sounds like you are looking to do something that will more likely require SUM or SUMIF.

    You can use many formulas is a “three-dimensional” way in Excel. That is, you can run them not only across a range of cells, but also across a range of sheets. For example, “=SUM(Sheet1:Sheet3!A1:E1)” will give you the sum of all cells in the range A1 to E1, on sheets Shet1, Sheet2, and Sheet3.

    Anyone know if SUMIF works across sheets? I don’t have anything handy to test it.

    Will one of those work for you?

  30. Chandoo says:

    @Srinivas: Welcome to PHD and thanks for asking a question. Here is how you can solve the problem if the employees are in the same rows in each sheet. Assuming the salary information is C2 for employee “srinivas”, you can write a formula like =SUM(Sheet1:Sheet12!c2) to sum up all the values across the sheets.

    Unfortunately this technique doesnt work with formulas like COUNTIF, SUMIF, SUMPRODUCT or array formulas. So, if the employees are not in the same rows in each sheet, you may need to use excel’s consolidation features to summarize the data.

    @Dustin: As you can probably understand from above, the SUMIF, COUNTIF and countless other formulas dont work with 3d references. Even when you define named ranges with 3d refs, they dont work.

  31. Dustin says:

    Chandoo’s comment on consolidation features got me thinking you may be much better off to just put all the data into one worksheet and then build a pivot table from that sheet. If you’re comfortable with pivot tables, go for that. It should take all of 3 minutes tops.

  32. Dave says:

    Hi Chandoo, – after spending hours searching the web, I came across your site, and am very impressed with how you explain how to use the various formulas. I have a query and am not sure if it can be solved at all. I am a cyclist and when we get race results, they come in an excel spreadsheet, with up to several hundred entrants. I also have a list of all our club members – about 120, – on a second spreadsheet. I then go thru the results spreadsheet and highlight all members that appear (not all members race) which takes me forever, – so that I can forward the results to each member. I have looked at arrays, conditional formatting and the lookup formulas, but haven’t had much success! Can you please help!!! :-)

  33. Chandoo says:

    @Dave… thanks for asking a question. I am surprised you said you have tried conditional formatting but didnot succeed. You can do this using conditional formatting. Here is how:

    1. define the club members list as a named range say, “club-members”
    2. In the main sheet, select all the cyclists participating in the race and go to conditional formatting. Select formula as the type of condition and then write something like =countif(club-members, B1)
    3. Set the desired highlight format
    4. hit OK.

    This should work pretty straight forward as long as you do not make any errors.

    Please read this article to learn more about conditional formatting.

    http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

  34. Dave says:

    Hi Chandoo, – so simple ………….when you know how!!!! – THANKS very much!!! works like a charm! – by the way congrats on your multiplying cells!

  35. jai krishnan says:

    i required a excel formula to match 3column data and refer another 3column daat together and find higher number in the 3column

  36. Rjhindinger says:

    This is an incredibly useful site. But I have a question about VLOOKUP that I don’t see answered here. I will be handed a table with a very long list of part numbers, and will need to pull cost from a corresponding row. The issue is that there will be four identical rows for each part number. Will this cause an exact match VLOOKUP to fail, or will it just happily report the first match it comes to?

    Thanks!

    • Chandoo says:

      @Rj… Welcome to PHD…

      You almost guessed answer for your question. Vlookup works find and gets you the first occurrence of the part number as long as there is one. Go give it a try.

  37. [...] most scenarios, we need sequential numbers to do something else (like passing them to an INDEX or OFFSET formula). Often we use helper column with the sequential numbers to do this. But by using ROWS() formula, [...]

  38. I just wanted to say your article illustrations are pretty cool. in this world gone digital a little taste of good old handwriting is always welcome

  39. Chandoo says:

    @Jerome… Thank you :)

  40. Wrecking Crew says:

    Awesome site, I am so glad I stumbled upon it. I do have a question I have been unable to solve looking around and was originally thiking some combination of Vlookup and Offset would work.

    I have two columns of data on one tab which reprsents a 1 to many relationship (column a has a primary key column b then has the sub keys associated with each primary key)

    i.e. Column A

  41. Wrecking Crew says:

    i.e.

    Column A Column B
    Order 1 Part 1
    Order 1 Part 2
    Order 1 Part 3
    Order 2 Part 4
    Order 2 Part 5
    Order 2 Part 6

    Then I was hoping to be able to use a combination of Vlookup and Validation on another worksheet tab where they would select a drop down list in column a which would be the options in column A here, once select column B would have a drop down list of only the column b which match the column A value Selected

    Drop Down A chosen Order 1, Column B Drop Down lets you select Part 1, Part 2, or Part 2

  42. Chandoo says:

    @Wrecking Crew: Welcome to PHD and thanks for the love.

    You can do this by using named ranges and offset formula. here is a tutorial: http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/

    Let me know if you have more questions.

  43. Aishwarya says:

    can anyone please explain me the difference between v look up and pivots.

  44. Hui... says:

    Vlookup is generally used to lookup a single value from a list which may contain several fields of data

    Pivots are used for displaying summations of large sets of data, where there is many rows of same data, ie: date, salesman etc
    Pivots allow you to interactively drag new fields in/out to rearrange your summation requirements. Pivots also allow for limited drawdown into the data to extract the individual records that make up a particular summation.

  45. Chandoo says:

    @Aishwarya… Also checkout this article to understand what pivot tables are all about.
    http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

    @Hui.. thank you :)

  46. Niladri Deb says:

    Hi
    I have been following your posts closely and must admit that I gained a lot out of it.

    Need your help in trying to figure out the following.

    In one sheet I have the list of all the pharma companies in USA. In a separate sheet (same workbook), I have the list of major pharma companies across the globe.
    Is there any function where I can quickly check whether “company A” mentioned in sheet 1 is also there in “sheet 2″? I tried match function but without success.

  47. Chandoo says:

    @Niladri.. thanks for your compliments. Welcome to PHD…

    Assuming the list of companies across globe is a named range “globalPharmaCompanies”

    in the sheet where you have companies in USA, insert a new column next to company name and write
    =countif(globalPharmaCompanies, a1)
    and drag the formula along.

    I am assuming column A has the companies in USA.

    You can also use conditional formatting to highlight the companies that are present in both lists – see here for more help on that – http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

  48. Jaron says:

    Hi, I wonder if someone can help me.
    I have a master listing of names and these people work in groups.
    I receive a report which details which days of the month they have worked.
    I receive the report in a differing order to my master listing so I match it up in the same way as my master by just cutting and pasting into the same order.
    What I want to do is be able to match up the days from 1 to 31 against each employee in a quick way, but the only way I’ve managed via Vlookup is by having to enter a formulae individuallly into each cell?
    I’m thinking there must be an easier way to do this – can anyone help and save me the time so that its a simple process each month to match up the new monthly data with each individual?
    Thanks in advance for any assistance.

  49. Chandoo says:

    @Jaron… Are you typing manually because each column (or row) requires a new date (ie vlookup(1,range,x,false), vlookup(2,range,x,false)… etc.?

    If so, you can use COLUMNS() or ROWS() formula. See this example to get an idea.

    http://chandoo.org/wp/2009/08/17/rows-and-columns-excel-formulas/

  50. Jaron says:

    Hi Chandoo,

    Thank you for your quick response.

    Please note that the formula I’m currently using is =VLOOKUP($A$5,’Monthly Timesheet’!$1:$65536,5,FALSE). I tried to copy the formula along, but the “,5,” doesn’t increase therefore I had to update it in each cell? To select the row’s formula would i need to insert this to replace the 5 above as in Rows(‘Monthly Timesheet’!E1:E31) ?

    Thanks in advance again!
    Regards
    Jaron

  51. Chandoo says:

    @Jaron: You have to keep one part of the ROWS static using $. For eg. this will work,

    =VLOOKUP($A$5,’Monthly Timesheet’!$1:$65536,4+ROWS(’Monthly Timesheet’!$E$1:E1),FALSE)

    You can learn more about the usage of $ and how it works here: http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/

  52. Jaron says:

    Thanks Chandoo, but its the 5 in the formula before false that is not increasing by one “=VLOOKUP($A$5,’Monthly Timesheet’!$1:$65536,5,FALSE)”. The formula just takes the same cell from the same column when I drag the formula along rather than copying the same information from the other spreadsheet in the next column but on the same row?

    Looking forward to your response, many thanks.

  53. dustin says:

    Jaron, I’ve solved that before by adding a reference to the area that I’m dragging across.

    So if your formula resides in B1:B10, in A1 I’d add a “=1″, and in A2 “=A1+1″. Copy the formula from A2 into A3:A10.

    Your formula in B1 then changes to “=VLOOKUP($A$5,’Monthly Timesheet’!$1:$65536,A1,FALSE)”, and you can drag or copy into B2:B10.

    There’s also formulas that can find what column or row the current cell is in, you could probably use those is its not appropriate to have a counter in the spreadsheet somewhere. But generally I find there’s a column or row somewhere that I can use for this.

    Hope this helps.

  54. Chandoo says:

    @Jaron… you can replace ROWS(’Monthly Timesheet’!$E$1:E1) with COLUMNS(’Monthly Timesheet’!$E$1:E1) and drag sideways.

    I am not sure if I am helping you as it is difficult for me to visualize your problem…

  55. Jaron says:

    Wow thanks, I think I’m just about there with the below forumla:

    =VLOOKUP($A5,’Monthly Timesheet’!$1:$65536,COLUMNS(‘Monthly Timesheet’!$E$1:E1),FALSE)

    The only problem I have now is that it pulls in the data from Columns A to D from the spreadsheet of data I’m copying into the master. In my master i have the Columns A to D fixed in the order that they need to be, when I use the above forumlar it takes that data from the other sheet but includes the columns A to D, when I only want it to match the Data from E to AI against the data in columns A to D in my master.

    I hope that makes sense and you are able to assist – the alternative I guess could be to hide those columns on the master? once the action is carried out?

    Thanks again for all your help so far!

  56. Jaron says:

    Another question on this one, I use find and replace to format repetitive cells in my original sheet – but once that is copied to my master using the Vlookup function, the find and replace doesn’t work as it doesn’t find the outcome of the forumla only the formula I guess if that makes sense?

    So is there a way when using Vlookup to get it to take the exact cell format along with the data within it when copying it into another sheet, or is there another way to format cells which have differing formulas within, but giving the same outcome showing in the cell. For your info, the cells which I’m trying to format all have ‘NR’ within them.

  57. [...] INDEX formulas to fetch corresponding weekly values for all years for selected region. Thanks to MATCH, INDEX and HLOOKUP formulas, this is not such a big task either. And if the optional comparison [...]

  58. Jaron says:

    Chandoo,
    I’ve discovered conditional formatting from your site, which I’ve used the formula is equal to NR to get the cells with the same value in the same colour! Excellent!.

    A slightly more difficult one though is I want it to highlight in yellow any cell with more than NR i.e. NR/TRAIN, NR/STNDBY etc – will always be NR/ then something – but I’m unsure what formulae to type in? Any ideas/tricks?

    THanks in advance! Again!

  59. Chandoo says:

    @Jaron.. I guess your earlier questions were answered by conditional formatting.

    You can use CF to define rules based on condition like “NR/TRAIN” etc.

    Select all the cells, go to conditional formatting, define a new rule that says

    cell contains “NR/” and adjust formatting. Make sure this rule is above the rule that says “NR”. That is all.

    You can see some very good examples here: http://chandoo.org/wp/tag/conditional-formatting/

  60. Eric says:

    Hello, looking for some introductory assistance here.

    Is there any way to do a validation (list) using information from workbook_1 on workbook_2 without having to pull that information onto workbook_2 and hiding the cells to make the list? Any assistance would be greatly appreciated.

    Thanks!!

    Eric

  61. Eric says:

    First of all Chandoo, wanted to let you know that I love your site and it has already helped me extensively with everything I am trying to do!!! Thank you!

    I have a different question though and not certain if it can be resolved with vlookup’s or if it needs to be a combination of different ones. Any help would be wonderful!

    What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in colum D) and then copy the row from A to I for each occurance during that range of dates so we can see how many and what errors have occured during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I am new at this so I don’t know if this can be done with vlookup’s or if you would need to use another function.

  62. Jaron says:

    Chandoo, many thanks for that – i’ve managed to work it out – amazing – saves me such a lot of time!

    Many thanks and keep going with the site – its so useful and have only just recently come across it but will be recommending it to my friends!

    Take care

  63. Chandoo says:

    @Eric 1: You can try using named ranges….

    @Eric 2: Thanks for the love. I am very happy you enjoy PHD.

    You may want to use data filters for something like this. They are simple and intuitive.

    @Jaron… :) glad to help.

  64. Singer says:

    I have used vlookup and hlookup extensively, including many nested uses of them. What I am trying to do is write a vlookup that will reference not only the first column but also the second.

    So for example (to keep this simple, but note I am not using dates)
    Column one can be month (jan, feb, mar) and column two can be dates (1,2,3) and I need to reference feb 2 (aka column a will say feb and column b will say 2).

    With vlookup either of these would be easy, but I need to now reference both at the same time.

    Any help would be greatly welcomed.

  65. baynoli says:

    Hi I really find your sites very helpful and interesting, i can’t imagine that there many things you can do in excel. but my question is how did you make those comments although its not quite related to excel. Do you use any software for your call-outs in the images. I mean those blue arrows and colorful notes. thanks. i’m glad i found this site.

    baynoli
    philippines

  66. [...] Day 10: Introduction to VLOOKUP Formula (and MATCH, OFFSET Formulas) [...]

  67. Ajit says:

    Usingh this formula:-
    LARGE($O$13:$O$2012,ROW()-ROW($BG$13)+1
    Show the value in Desending Order.

    I also want to show the other particulars of large number as so on.

  68. Mike says:

    I have a question I am trying to retrieve information from a Three Dim-Problem.
    SheetA Contains
    ColA = Branch info (e.g LNC)
    ColB = Part info (e.g exrt)
    ColD = Has a date title (Aug sales) Which will be from Inventory Movement (Sheet B)

    All three Cols are part of the criteria to populate ColD from Inventory Movement
    Any Ideas

  69. Chandoo says:

    @Mike… I am not sure I understood your problem. Can you tell me what is the structure of Sheet B and what information you want to fetch? If you want the sum of values meeting a certain criteria use either SUMPRODUCT or SUMIFS. Examples here:

    http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
    http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/

  70. Ramki says:

    Hi!!
    Could you please explain about “double lookup” by using Index/Match functions?

  71. Hui... says:

    @Baynoli, Mabuhay
    The Font is Akbar http://www.wobblymusic.com/groening/akbar.html
    The arrows are from either Excel or Paint.net http://www.getpaint.net/

  72. Hui... says:

    @Ramki
    The Index,Match combination is often used to do a 2 dimensional lookup, where you are looking up a value in a data table and either know the Row or Column (1 dimensional) or don’t know either the row or column 2 dimensional)
    You can also use the Offset, Match combination to return a similar result.

    Have a read of
    http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

  73. I follow the logic but why not add a worked example especially offset where it is not really obvious why you would use it

  74. Hui... says:

    @John

    Offset returns a range as a result and so unless the range is only 1 cell, will never give an answer by itself, so it has to be used with something like a Sum, Min, Max, etc to convert the range to an answer.

    Index is great for looking up individual cells within a Range

    Match is used with either Offset or Index to determine the locations within a range or from a Reference point to the point you want.

    I have put a few example of both uses in the attached file:
    http://rapidshare.com/files/410294277/Offset___Index_Examples.xlsx.html

    Once you think you have got your head around them, have a read of:
    http://chandoo.org/forums/topic/uni-assingment-help-matching-prices-for-shopping-items

  75. venugopal G says:

    How to become an Excel Guru in 31 Days :

    I want to be part of the course , Please advice.
    I am a dummy and i have been given a task of learning excel in just 60 days.

    Regards
    Venu

  76. Pete says:

    Hi All

    Not quite sure how this is done, but pretty sure it’s with offset, hence the message!
    I want to calculate the variance between two columns for a monthly report (i.e the weekly variance), short of changing the calculation manually, I’m not sure of the best way to “automate” this…

    So: Week 1 var = Week 1 – Prior End of Month, Week 2 Var = Week 2 – Week 1 and so on…

    Any ideas would be greatly appreciated!!!!
    Pete

  77. John Franco says:

    Hi Chandoo,

    Great explanation!

    I found a fun analogy to explain what a Vlookup function is…

    Vlookup = your index finger

    Why?

    Imagine you are looking for “Susan Wilson” phone number. So you open the city phone directory and start searching (looking up).

    Here is the correspondence of the Vlookup arguments with reality

    lookup_value= “Susan Wilson”
    table_array= the phone listing page
    index_col_num= the column to the right with all the phone numbers
    range_lookup= the fact that you are looking for “Susan Wilson” and not “Susan William”, “Sue Wilson”, etc

    Vlookup result= “Susan Wilson” phone number

  78. John Franco says:

    Just to add to my comment above.

    I uploaded a video that may help:

  79. Ramki says:

    Hi!! Chandu!!
    This is Ramki again! My concern as follows….
    I have two sheets of data… Sheet 1 as follows.
    Order No. Item code Item Qty.
    123 544 Jam 55
    456 645 Sauce 24
    678 234 Soaps 12

    In sheet 2
    Ord.No. Item Code Item Qty. Alloc.Qty
    123 544 Jam 55 32
    456 358 Shampoo 24 16
    678 266 Skin Cream 12 6
    I have to get alloc.qty in Sheet1 with ref.to sheet2 by considering two look up values Viz. Ord No. and Item code…
    Pls help…

  80. Hui... says:

    @Ramki
    If I read you right you want to get the value 32 from Sheet 2 and put it besides 55 in Sheet1!E2

    if so try
    C2: =SUMPRODUCT(1*(Sheet2!$A$2:$A$20=Sheet1!A2)*(Sheet2!$B$2:$B$20=Sheet1!B2)*(Sheet2!$E$2:$E$20))

    And copy down
    Change $20 to suit your data ranges

    If I haven’t read you correctly please explain again

  81. Top10Lists says:

    Great posts! I would like to also point you to some great references regarding VLOOKUP: http://www.squidoo.com/best-excel-2007-vlookup-references

    Hope this help some people here!

  82. [...] these excellent articles explaining – VLOOKUP, OFFSET & MATCH, [...]

  83. [...] OFFSET formula is one of the most powerful and useful formulas in Excel. It is a must if you work with dashboards, reports or advanced excel models. In this article, Gregory from Excel Semi Pro explains how to use offset formula. (check out alternative explanation for OFFSET formula) [...]

  84. Brian says:

    I just stumbled upon your site and signed up for your emails. From what I’ve seen your site is very complete with straight forward explanations and examples.
    Question, I use Vlookup and IF statements to fill a form/report on a different worksheet. We add comments to some cells in the record worksheet. Is there a way to show those comments in the form on the other worksheet?
    Keep up the good work, and thank you.

  85. [...] Excel Tips & Tricks 2. Excel Pivot Tables – Tutorial 3. 51 Excel Formulas in Plain English 4. VLOOKUP Formula for Dummies 5. Free Excel Chart Templates 6. Excel Tutorials for Beginners* Advanced Excel Tricks 1. Excel [...]

  86. Sami says:

    Good morning! We currently use a vlookup to go between 2 workbooks. for the first workbook we use =VLOOKUP($A3,’2011 UMTS Master Tracker.xlsx’!Table1,108,FALSE)
    the 2nd workbook has the values and works great until the PM decided to put If formulas to figure out dates in the cells.
    So column 108 has this formula in it: =IF(DW8>0,DW8,IF(DJ8>0,DJ8+3,DI8+3)) but it displays the date 03/15/2010
    becuase of this fomula we cannot get the vlookup to populate the 03/15/2010 on the first workbook cell.
    Do you have any clue why it works if we have hard coded dates in the columns but when the IF formula is in the culomns we get

  87. Luis says:

    Need some help! As I am new in Insurance Dept. I want my work to be easier in anyways.
    I am now handling more than 60,000 employees and we are using employee no. for logging employee with insurance. In 1-60,000 list i have we have to delete 5,000 employees. Now any formula to highlight the 5,000 employee in 60,000 list i have? Thanks

  88. Ionel says:

    Hi Chandoo,

    I have data in three columns: A,B,C and I want to get the average of the closest two values out of three in each row. Could you help me with a formula for this?
    many thanks in advance!

  89. Chandoo says:

    @Luis.. see http://chandoo.org/wp/2010/06/17/compare-2-lists-in-excel/ for some ideas

    @Lonel: I could solve this problem with one helper column that shows the value of first column. See the example file: http://img.chandoo.org/playground/average-of-2-closest-numbers.xls

  90. Ionel says:

    Dear Chandoo,

    This is really great help and I appreciate it very much!
    Very best wishes,
    Ionel

  91. newexcell-or says:

    HI,
    I need a help with this data : columns are as follows:
    salesman, brand, month, sales
    ABC b1 Jan 120
    ABC b2 Jan 130

    Now I have an obligation to use Vlookup and the challenge is to calculate total sales of salesman ABC in the month of Jan.
    Please help!!!

  92. Hui... says:

    @ Newexcell-or
    If your really hung up on using VLookup have a read of:
    http://chandoo.org/wp/2010/11/02/multi-condition-lookup/

    But for Total sales I’d be using Sumproduct or Sumifs for your problem

  93. [...] I am asking you a tricky formula question. This is asked by Ionel on the Introduction to VLOOKUP, OFFSET & MATCH Formulas [...]

  94. K says:

    I have spent most of the day searching for an answer to what seems to me like an easy question. You seem to have some of the best answers for the questions listed and (kudos to you!) seem to be prompt as well.

    I have a large worksheet 7,708 rows columns A through AQ. Column B is a four digit number used to designate a district – there are usually 15-25 rows with the same district code and different store numbers.

    I have six employees reporting to me that I pull out the data for the districts to get totals, averages, etc for each of the columns. As an example employee Smith is responsible for districts 1234,1235, 1236, 1237 and 1238. I want to know total products sold for all of his stores in those districts. I’m using an auto filter with subtotal formulas (designated 101 and 109 for sums and averages to ignore the hidden values.)

    What I need to be able to do is find each of the districts that Smith is responsible for and replace it with his name in every occurence. The find and replace function works just fine for this except….. I want to do them all at once. It’s very time consuming to find 6-8 district codes for 6 different employees and replace with their name – and I have to do it for additional worksheets just like that coming through on a daily basis.

    So I was hoping there’s a way to type in multiple values into the find/replace function – separated with a comma, colon, semicolon, etc doesn’t seem to work. Maybe it’s impossible, but there’s no way to know with out asking…

    I’m using 2003.

  95. Peahead says:

    I used the following to solve Lonel’s Average of Closest Numbers issue:

    Data Stored in cells B3:D18

    E3 = IF(ABS(B3-C3)=Min,(B3+C3)/2,IF(ABS(B3-D3)=Min,(B3+D3)/2,(C3+D3)/2))

    Min = MIN(ABS($B4-$C4),ABS($B4-$D4),ABS($C4-$D4))

    • Naveen Kumar Pokala says:

      This is the formula I came up with….

      case-1:: if you need average of closest (first 2 MIN numbers) of a series:

      =AVERAGE(MIN(D1:D12),SMALL(D1:D12,2)) where D1:D12 represent the series

      case-2:: if you need average of closest (first 2 MAX numbers) of a series:

      =AVERAGE(MAX(D1:D12),LARGE(D1:D12,2)) where D1:D12 represent the series

      regards,
      naveen

  96. Peahead says:

    Ops…
    E3 = IF(ABS(B3-C3)=Min,(B3+C3)/2,IF(ABS(B3-D3)=Min,(B3+D3)/2,(C3+D3)/2))
    Min = MIN(ABS($B3-$C3),ABS($B3-$D3),ABS($C3-$D3))

  97. Animesh Anand says:

    To get the average of two closest numbers, copy the following formula and paste in D2

    =IF(MIN(ABS(A2-B2),ABS(A2-C2),ABS(B2-C2))= ABS(A2-B2),AVERAGE(A2:B2), IF(MIN(ABS(A2-B2),ABS(A2-C2), ABS(B2-C2))=ABS(A2-C2), AVERAGE(A2,C2), AVERAGE(B2:C2)))

  98. Emil Martinas says:

    Solution for “Find the Average of Closest 2 Numbers out of 3 [formula challenge]”

    In D2 I wrote the following formula:

    =IF(SMALL(A1:C1;2)-SMALL(A1:C1;1)

  99. newexcell-or says:

    Hi Chandoo,

    Here is another issues I am facing right now, I have the data with state and sales. I want to use VBA here and want to create a button which when clicked should split the sheet state wise in different sheets. Example : all data is in sheet 1 and when the button is clicked the data for Madhya Pradesh should be in sheet 2 and Andhra in Sheet 3..similarly…I am able to create new sheets using VBA but this splitting is a problem!!
    Could you help me ??

    Thanks in advance :)

  100. MMMM says:

    Two possible solutions, admittedly written without too many bells and whistles.

    First solution:
    =IF(MAX(A2:C2)-MEDIAN(A2:C2)MEDIAN(A2:C2)-MIN(A2:C2),SUM((MIN(A2:C2)+MEDIAN(A2:C2))/2),”XX”))

    Because there are only three numbers, Max, Min and Median will return them all. “XX” would be where there is equal “closeness”, so (e.g. 6, 8, 10). You could always replace the “XX” by using CONCATENATE with both possible TRUE answers from the 2 IF functions, plus an ‘ or ‘ between them (e.g. “7 or 9″).

    Second solution: (probably what the post by Emil started)
    =IF(SMALL(A2:C2,2)-SMALL(A2:C2,1)

  101. MMMM says:

    For some reason, not all the post I wrote has appeared (and ‘coincidentally’ it has cut it off at the same point as Emil’s). Very odd. SO the second formula is incomplete.

  102. MMMM says:

    And so’s the first – it hasn’t all appeared. What am I doing wrong?

  103. MMMM says:

    One more try – 1st solution:
    =IF(MAX(A2:C2)-MEDIAN(A2:C2)MEDIAN(A2:C2)-MIN(A2:C2),
    SUM((MIN(A2:C2)+MEDIAN(A2:C2))/2),”XX”))

    2nd solution:
    =IF(SMALL(A2:C2,2)-SMALL(A2:C2,1)

  104. Nicky says:

    Hi Lonel,

    You could try the following formula in cell D2 assuming your input data is in cells A2:C2 :
    =IF(MEDIAN(A2:C2)-MIN(A2:C2)

  105. Mpemba says:

    =SUM(MIN(A1:C1)*((MAX(A1:C1)-MEDIAN(A1:C1))>(MEDIAN(A1:C1)-MIN(A1:C1))),MEDIAN(A1:C1),MAX(A1:C1)*((MAX(A1:C1)-MEDIAN(A1:C1))(MEDIAN(A1:C1)-MIN(A1:C1)),(MAX(A1:C1)-MEDIAN(A1:C1))

  106. Mpemba says:

    OK – why was formula cropped?

    =+SUM(MIN(A1:C1)*((MAX(A1:C1)-MEDIAN(A1:C1))>(MEDIAN(A1:C1)-MIN(A1:C1))),MEDIAN(A1:C1),MAX(A1:C1)*((MAX(A1:C1)-MEDIAN(A1:C1))(MEDIAN(A1:C1)-MIN(A1:C1)),(MAX(A1:C1)-MEDIAN(A1:C1))

  107. ZSB2011 says:

    =AVERAGE(SUM(A1:C1)-SUM(MIN(A1:C1),MAX(A1:C1)),IF(ABS(SUM(A1:C1)-SUM(MIN(A1:C1),MAX(A1:C1))-MAX(A1:C1))>ABS(SUM(A1:C1)-SUM(MIN(A1:C1),MAX(A1:C1))-MIN(A1:C1)),MIN(A1:C1),MAX(A1:C1)))

  108. Sukhdev Kaintyura says:

    Dear Chandoo,
    Greetings for the day! I have read the answers asked by visitors are very impressive. I have also a question. hope you will get it cleared me.
    I want to use a filter from one sheet to another sheet by the name of Brach. As I use filter in 1st sheet, only that branch’s data should be reflect in 1st sheet from 2nd sheet. Kindly help.

  109. [...] Alberto used IF formulas to fetch the values for each half-year (semestre). Instead using INDEX or OFFSET would make his job [...]

  110. Alison says:

    hi – i need some help with a vlookup – i have a column that is a vlookup on a result. then I have the following column with =VLOOKUP($BD2,curve,4,FALSE)*Z2 – this cell is to take the result from the column BD (which itself is a vlookup) then match this result to a certain outcome that will be multiplied by a number.

    I’m wondering if the problem is that the vlookup is form a cell with a vlookup??

    hoping you have a solution!!

  111. Hui... says:

    @Alison
    What your trying is quite possible
    Does the value your lookup up from BD2 exist in the Curve Named Range
    What is the Curve Named Range?
    What Formula’s are in Column 1 of Curve?
    Can you post some of the data or your file ?

  112. Alison says:

    I think i solved it – not sure if it’s the most “elegant” of solutions – =VLOOKUP((VALUE(LEFT(BD2,4))),curve,3,FALSE)*Z2

    i think then i am converting the formula cell into a value in order to do a vlookup

  113. Alison says:

    and the formula in BD2 is – =VLOOKUP(M2,SLMresult,4,FALSE) – it’s a different table

  114. Angi says:

    Chandoo, I have had a hard time wrapping my head around match and offset, so I really appreciate all you’ve put together here; it’s helped me a great deal.

    However, I believe I’ve found an error in the offset range where you say, “…OFFSET (A1, 3,4, 5,6) would return 5×6 cell range from E4 (A+4 columns, 1+3 rows = E4) thus: E4:J9″
    –>> Starting the range at E4… 5 rows = rows 4, 5, 6, 7, 8, so E8 is the bottom left cell in the range. 6 columns = E, F, G, H, I, J, so J8 is the bottom right cell in the range, hence, the range should actually be E4:J8.

  115. Hui... says:

    @Angi
    OFFSET (A1, 3,4, 5,6) = E4:J8
    is correct

  116. Santz says:

    How to use Index & Match functions with Multiple criteria in excel?

  117. Prat says:

    Can anyone please tell which function to be used and how

    when I write Apple: I should get the result beside apple as fruit
    Dog:I should get result as Animal
    Pencil:I should get result as Thing

    • Shrey says:

      @prat if you have your table in A1:B4,
      Use some Cell D3 to enter Apple, Dog, or Pencil
      Your formula should be =VLOOKUP(D3,A2:B4,2,TRUE)
      Depending upon what You enter in D3 you will get appropriate answers

  118. Hui... says:

    @Prat
    You can use a VLookup or an Index/Match or Ofset/Match function
    You will need a list of the Item and Group in two columns

    This is probably a great spot to start:
    http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

  119. Prat says:

    gr8 it works

  120. Rajeev says:

    Hii chandooo….

    day by day im becoming proficient in excel just because of you….
    I ahve one question…i think u can help me on this…how to embed a calender in a cell….also explain if we can do it for multiple cells….i’m waiting…..

  121. Tithi says:

    Hi

    Can u pls tell me how can I calculate autocorrelation function for a data series in excel,

    i need to check the seasonality factor in data series of sale points since there r lots of such data seires, I do not wanna use graph n use autocorrelation. Pls help

  122. TeamMP says:

    Hey man, this is great! Very informative and fun to read. Thanks alot!

  123. Elliott Bailey says:

    Thanks a lot mate! Loved the simple approach!

  124. [...] VLOOKUP(), MATCH() and OFFSET() explained – somewhat [...]

  125. Hartke says:

    I just ran into an issue using INDEX(MATCH) – MATCH returns the number of the row its in, not its relative position in your defined array. But INDEX then applies that value in reference to its array.

    So when I wrote this formula: =INDEX(A2:J50,MATCH(F2,E:E,0),9),0) it returned the value for the row directly below the one that I wanted! It took a while to sort that out, believe me. Writing it as =INDEX($A$1:$J$5000,MATCH($F2,E:E,0),9),0) put them back in alignment.

    tl;dr -When using INDEX(MATCH) always start your reference in row 1!

  126. munnu says:

    Dear mr.chandoo

    hi,
    can u plz give me u r contact mail add in case if I want to have solution for excel sheets.

    Regards.

  127. [...] a match is found, we show the latest NAV for that fund in the tracker worksheet. And of course, we use VLOOKUP to find the [...]

  128. Vinod says:

    Hi all,How to avoid spaces in dynamic generation of data?

  129. Anitha says:

    Hi Chandoo,

    I am the begginner for for excel. Currently I am focusing on learning formulas…I am feeling difficult in understanding….Kindly let me know how do I learn these formulas and become an expert…and also I would like to know how to combine the formula’s when ever required

    Thank you in advance

    Regards
    Anitha

  130. kay67 says:

    Hello Guys,

    I read and re-reread the topic on OFFSET but I still dont get.
    Van someone explain in plain layman english with concrete example?
    When is it used and how to check if the formular is correct.

  131. Sandy says:

    What formula would you use if you want to pull out everything that has a matched code? For example, a database of location codes and names. I want to list all names that match the location I’m looking up.

  132. [...] I used a match function to convert letter grades (ex. A, B+, C-) to numerical grades (ex. 100, 88, 72) as shown in Ref1. [Ref1] [Ref2] [...]

  133. semaema says:

    dear sir, can you give me  the different between vlookup and hlookup and its purpose

  134. SGK says:

    hello,

    please help me to find what formula should I use to solve my problem. 
    basic table of my file is below, I want to find the last date of activity marked with X according to each person.

    Name Surname
    1-Jun
    2-Jun
    3-Jun
    4-Jun
    5-Jun
    RESULT

    A
    X
    X
     
     
     
    2-Jun

    B
    X
     
     
    X
     
    1-Jun

    C
    X
     
    X
     
     
    3-Jun

    D
     
    X
     
    X
     
    4-Jun

    What formula should I write in column RESULT to find that values???

    thank you in advance 
     

    • SGK says:

      sorry, I think table is broken, table simply is below:

      Name Surname | 1 Jun | 2 Jun | 3 Jun | 4 Jun | RESULT
      A                     |   X     |   X    |          |         |  2 Jun 
      B                     |   X     |         |          |  X     |  4 Jun 
      C                     |   X     |         |    X    |         |  3 Jun
      D                     |          |    X   |          |   X    |  4 Jun

  135. [...] know that using VLOOKUP, we can find a value corresponding to a given item. For example Sales of x. But what if you have [...]

  136. Annie says:

    In your example of Area / Project, I want to select an area as ‘Marketing’ and then I want all marketing projects to appear in the next column as:
    Marketing | Hoziron
                     Cosmos
                     Spring

    Could you please guice me how I can do that?
    Regards
    Annie

  137. leigh metcalfe says:

    I need help with a formula to look in a spreadsheet, and do the following.

    line 4 is always the date but can range from K:Z

    Line 6 – 40 can have different qty’s in and will contain spaces

    on the same line but in cell D it will have a description of the product.

    I need it to if i put a date in A1 it will look down down the correct column and then put the qty and description into another spreedsheet in the same workbook. I can email the workbook if needed

  138. Quora says:

    Is it possible to do an excel sensitivity analysis with three variables?…

    I agree with the previous answers – there is no inbuilt Excel function like the awesome two-variable tables to provide a dynamic sensitivity analysis for three or more variables. That said, you can use Scenarios to construct different scenarios with di…

  139. BoredGourd says:

    WOw! SUper. I love you! EXACTLY what I needed. THANKS!

  140. hi
    any help would be gladly appreciated

    I need to find designated OM for each supervisor in any given month with same ID below is the table

    Roster Reference

    Month
    ID
    Supervisor
    OM

    September
    625222
    Gina
    Karen

    October
    625222
    Gina
    Thomas

    November
    625222
    Gina
    Terry

    December
    625222
    Gina
    Carl

    September
    654466
    Cole
    Carl

    October
    654466
    Cole
    Terry

    November
    654466
    Cole
    TJ

    December
    654466
    Cole
    Karen

    Actual Table
    I tried using this formula =INDEX($D$3:$D$10,MATCH(A16,$A$3:$A$10,0)), however im only getting correct OM match for Gina.
    is another formula i can use so i can include the supervisor’s ID as reference aside from month?

    Month
    ID
    Supervisor
    OM
    Sale

    September
    625222
    Cole
    Karen
    1

    September
    625222
    Cole
    Karen
    2

    October
    625222
    Cole
    Thomas
    3

    December
    625222
    Cole
    Carl
    4

    October
    625222
    Cole
    Thomas
    5

    November
    625222
    Cole
    Terry
    6

    October
    625222
    Cole
    Thomas
    7

    November
    625222
    Cole
    Terry
    8

    November
    625222
    Cole
    Terry
    9

    October
    625222
    Cole
    Thomas
    10

    November
    625222
    Cole
    Terry
    11

    October
    625222
    Cole
    Thomas
    12

    September
    654466
    Gina
    Karen
    16

    October
    654466
    Gina
    Thomas
    15

    November
    654466
    Gina
    Terry
    14

    December
    654466
    Gina
    Carl
    13

    Many Thanks
    Kurt

  141. hi Sir Chandoo

    i apologize on my first post. I need to find designated OM for each supervisor in any given month with same ID below is the table
    Roster Reference Table

    Month, ID, Supervisor, Karen

    September,  625222,  Gina, Karen
    October       625222   Gina, Thomas
    November    625222   Gina, Terry
    December     625222 Gina,  Carl
    September    654466 Cole,  Carl
    October       654466   Cole, Terry
    November    654466   Cole, TJ
    December    654466  Cole,  Karen

    Actual Table (OM not included)
    I tried using this formula =INDEX($D$3:$D$10,MATCH(A16,$A$3:$A$10,0)), however im only getting correct OM match for Gina.
    is it possible to use both Month and ID as reference at the same time so i can get the correct OM for Sup? which should i use

    Month ID Coach OM Sale
    September  625222  Cole     1
    September  625222 Cole     2
    October      625222 Cole  3
    December   625222 Cole  4
    October      625222 Cole  5
    November  625222 Cole  6
    October      625222 Cole  7
    November   625222 Cole  8
    November   625222 Cole  9
    October      625222 Cole  10
    November   625222 Cole  11
    October      625222 Cole  12
    September   654466 Gina  16
    November    654466 Gina  14
    October       654466 Gina  15
    December 654466 Gina  13

  142. srinivas says:

    Hi Chandoo,
    I need your help, we have one Data base application, it contains 2 fields, year & instrument no & subbmit button. I have yr and instrument data in excel sheet. i use to enter manually one by one. instead of how can i enter large data all at a time Using VBA or macto or any other formula.
     
    A1 has 2010 year, B1 has 2546 instrument No.
    i used this =CONCATENATE(A1,B1,
     how to write for submit

  143. ml says:

    Hi,

    I would like to know if I can use excel to have a cell to work as a calculator, example: add an amount $235.00 once this amount is entered I want the cell come back blank ready for the next amount and the amounts entered in that particular cell need to be sent to another cell named total.

    Is this possible and how?
    Thanks in advance,

    ML

    • Hui... says:

      @ML
      It could be done but would require VBA to do it
      You would also need a C button to clear/reset I assume

      The following VBA code will do what you want
      Add the code to the Worksheet module of the Worksheet you want to use
      Change the addresses to suit

      Link then Reset code to a Shape or Button as appropriate

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = "$C$3" Then
      Application.EnableEvents = False
      [C5] = [C5] + [C3]
      [C3] = 0
      Range("C3").Select
      Application.EnableEvents = True
      End If
      End Sub

      Private Sub Reset()
      Application.EnableEvents = False
      [C5] = 0
      [C3] = 0
      Range("C3").Select
      Application.EnableEvents = True
      End Sub

    • Hui... says:

      @ML
      I changed the code so that a Single routine now does all the work
      Enter values as normal in C3
      they are accumulated in C5
      Enter a C or c in C3 and it clears C5 and resets C3 to 0


      Private Sub Worksheet_Change(ByVal Target As Range)
      On Error GoTo err
      If Target.Address = "$C$3" Then
      Application.EnableEvents = False
      If UCase([C3].Text) = "C" Then
      [C3] = 0
      [C5] = 0
      Else
      [C5] = [C5] + [C3]
      [C3] = 0
      End If
      Application.EnableEvents = True
      End If

      err:
      Application.EnableEvents = True
      End Sub

  144. ml says:

    Hi Hui !
     
    thanks for the reply. I want to ask you or tell you, that I want C5 to show the total amount entered in C3, but the trick is to have C3 blank after clicking onto the (+) sign. Then those amounts entered in C3 will show its total into C5, does this make sense?
     
    thanks a lot, 
    ML

  145. ml says:

    Hi HUI,
     
    Maybe i can send you my work sheet to your email and show you what exactly want to do, will this be possible?
     
    thanks in advance!

    • Hui... says:

      @ML
      Click Hui… above
      Email add is at bottom of the page

      I don’t understand what you mean by “but the trick is to have C3 blank after clicking onto the (+) sign

      There is no need for a + sign ?

      Please elaborate

  146. ml says:

    Hi Hui,
     
    I will explain more:
     
    I have my worksheet where i am going to add my expenses like gasoline, medical etc etc.
    gasoline   ($30.00 + $70.00) but after entering each amount, i want the (entry cell) to be blank, so when i enter the first amount what should i do in order for that (entry cell) to turn back empty cell ready to receive next amount? How the program has to be written?
    in the (total cell) i want the Total add up amount  that I had entered on the (entry cell)
      
    Medical    (entry cell)                      (total cell)
    entertainment (entry cell)                (total cell)
     
    on the entry cell is where i want to add the amounts, one after other and have it added up inside the total cell.
    Does this make sense?
     

  147. Cris says:

    Help! I need to match case type numbers to its corresponding case name. The problem for me is that some of the case type numbers have more than one case name that goes with it.  A vlookup only works when the case number only has one case name associated with it. How can I get the formula to return the case names if there is more than one? Thanks anyone for any help on this.
    Cris

  148. Danny says:

    Hi,

    I’m abit of an Excel Newbie and have been having real trouble figuring this out. Let me explain the problem:

    I have data in 3 columns of data each of 2 sheets of a workbook.
    Column Headers are:
    ID, Name, Amount

    What I need:
    When ID AND Name match between book1 and book2, ID,Name & are printed into Book3 and Book1 Amount is deducted from Book2 Amount.

    i.e. First I am asking for ID’s to match, if they match, then I ask for Names to match, when they also match, I am asking for Amount1 to be deducted from Amount2.

    Matching ID, Name and ‘Difference’ will be output into Book3.

    Hope you can help, vlookups, IF(ISERROR have had no success so far.

  149. M JJ says:

    I need to merge the 4 excel 2010 sheets in 5th sheet as a  Monthly fault type with the value of Fault type can u help?

  150. Maria says:

    Im just trying to set my excel sheet so when the date hits, itll turn red. I believe i got it so its yellow when within 90 days but now i need the dates that are passed to be red. Any help would be great!

  151. Steven Woods says:

    I have a worksheet containing rows and rows of data. I would like to extract the data to a second worksheet based off of the date and certain type. I think this requires a combination of an If statement and a vlookup. I am not sure how to put the formula together. Can you help?

  152. sampann says:

    Hi

    I need to drag the vlook up results on main sheet horizontly earlier used to add $ to make the cell reference freeze an only output cell would increse while dragging. kindly help in recollecting the exact syntax

    • Hui... says:

      @Sampann
      Can you post your formula here and tell us what it is doing right and what it is doing wrong?

      • sampann says:

        =VLOOKUP($C15$,Thermal!$A3$:$W22$,14,0)

        Iam trying to keep the refrence cell freezed and want to drag down the formula to show results on seperate sheet for marks of students in report card format

        sc
        maths
        history

        but when i drag the cell values change and i have to manually enter the changes in formula bar .. I did this function long time back in 2006 but not able to recollect i think iused $ sign to freeze the range lookup

  153. d_d_gravy says:

    I need to validate data on 2 points. First, it must be in a range, Second, it must be greater than the value in the cell next to it, while still in the range. My fields are T_Start, L_Start, L_End, and T_End. T_Start can have any value from the named range Times. L_Start can have any value from named range Times greater than T_Start. L_End must be greater than L_Start, and T_End must be greater than L_End, all while still in the range. The validation for T_Start would only include a reference to the named range, but the validation for L_Start must examine T_Start first, and only allow values from the range Times that are after T_Start. Can anyone offer any advice?

  154. kasusx says:

    thanx chandoo for your info

  155. Sankar says:

    I have a doubt, I have 100 associates who have entered the amount of work done for 30 days, now I want to list out all the associates who have entered less than 8 hrs of work a day along with the date which they have entered less than 8 hrs a day, how is that possible, do we have any formula’s or I just have to conditional formatting for each individual. please explain

  156. Les says:

    Hi-
    Have issue that doesn’t seem to be answered:
    Have lookup data in Col A:A; there are multiple templates copied there, each with a Header in A:A: but the data I need to (offset(match) is 4 rows down and 10 col over, consistently from each occurrence of the Headers…

    Offset doesn’t seem to like my substituting a Match function for the “Reference” portion of the Offset argument, even though the Match function, by itself, produces the proper Nth row down, (from which I want to go 4 more down, and 10 over, to get the data I need…:-) for every new Lookup I’m searching for…

    =Offset(Match(lookup,in A:A,0),4,10) keeps getting me ‘value” message…

    AAARRGGHHHH!

  157. SaifUllah says:

    Very good help matters

  158. Mohammed Usman says:

    The questions asked in the example was very much helpful in practicing VLOOKUP .

  159. Massimiliano Matrale says:

    Dear all, for the Homework, are these results right, please?
    Thanks in advance

    Massimiliano

    Homework
    Questions Your Answer
    1. How many sales for the person in cell G17? 1.316
    2. Who made more sales – Jamie or Jackie? 2.133 1.610 Jamie
    3. What is the sale per customer for Jagjit? 257
    4. What is the profit % for person in G17? 4,64%

    Jonathan

  160. Kameswaran says:

    Sir,
    We would like to prepare a excel for the display view of other team which picks the data based on a reference no entered by other team. The excel database it is referring is a continuous sheet appended every day. Do we have a formula to source the nth data based on the reference no. As vlookup returns only the 1st data we cant able to complete. Need your help. Thanks in Advance.

  161. Nabil says:

    can someone please help me. i tried following the instructions on this but i am not getting it for some reason. the day i learn this my dreams will come true. i have a scorecard that contains YTD data including different months in one column, and different vendors in another column and I would like to select one of those months, and one of those vendors and get the data located in other columns.

    as an example, just picture a table with 5 columns, with the following headers:
    Column 1 = vendors
    column 2 = Months
    column 3 = score
    column 4 = invoice amount
    column 5 = penalty amount

    If i want the score for vendor “michael” for the month of March. what formula will get me this? thanks so much in advance!!

  162. ApparentlyStupid says:

    Once again, no idea what any of this means. I can only follow so far then I get completely and totally lost.

Leave a Reply