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

Posted on November 19th, 2008 in Featured , Learn Excel - 228 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:

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

### What does VLOOKUP really do?

Imagine you have a list of data like this:

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 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.

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

## 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.

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

 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

 The new Mac vs. PC web ad by Apple is really cool Generating Random Phone Numbers
 Written by Chandoo Tags: Excel Tips, help, howto, MATCH(), microsoft, Microsoft Excel Formulas, OFFSET(), spreadcheats, spreadsheets, vlookup Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 228 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.

• Vinoth says:

How to prepare a dashboard with remaining day left for delivery & each Project manager will have different sheet, so that they a look the status in what stage exactly.

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

• what is the meaning of [mylist is sorted] ??/

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..

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.

• Zero says:

Both V&H lookup are used for simple solutions that don't require you to brainstorm much but each one of them can combine easily with 'if' for some specific situations that Index can't. Index can be considered as a combination of V&H lookup with great help from match to go through the limitations of the fomer but it has another limitation. It only can use in combo with match

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:

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().

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,

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. [...] MATCH Excel Formula - Tutorial & Examples [...]

29. 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

30. 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?

31. 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.

32. 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.

33. 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!!! 🙂

34. 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.

35. 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!

36. 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

37. [...] this, we will use VLOOKUP () formula. The simplest formula looks like this: [...]

38. 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.

39. [...] 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, [...]

40. [...] excel formulas: IF and Then, Vlookup, Offset, Sumif, Countif, Working with date and [...]

41. 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

42. Chandoo says:

@Jerome... Thank you 🙂

43. 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

44. 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

45. 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.

46. Aishwarya says:

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

47. 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.

48. Chandoo says:

http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

@Hui.. thank you 🙂

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.

50. Chandoo says:

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/

51. 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.

52. 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/

53. 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) ?

Regards
Jaron

54. 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)

55. 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.

56. 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.

57. 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...

58. 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!

59. 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.

60. [...] 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 [...]

61. 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?

62. Chandoo says:

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/

63. 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

64. 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.

65. 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

66. 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.

67. 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.

68. 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

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

70. 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.

71. 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

72. 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:

73. Ramki says:

Hi!!

74. 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/

75. 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.

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

77. 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

http://chandoo.org/forums/topic/uni-assingment-help-matching-prices-for-shopping-items

78. venugopal G says:

How to become an Excel Guru in 31 Days :

I am a dummy and i have been given a task of learning excel in just 60 days.

Regards
Venu

79. 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

80. John Franco says:

Hi Chandoo,

Great explanation!

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

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

81. John Franco says:

Just to add to my comment above.

I uploaded a video that may help:

82. 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...

83. 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

84. 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!

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

86. [...] 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) [...]

87. 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.

88. [...] VLOOKUP formula – using it, examples & advanced uses [...]

89. [...] VLOOKUP formula – using it, examples & advanced uses [...]

90. [...] VLOOKUP syntax & advanced uses, Examples of MATCH & OFFSET Formulas [...]

91. [...] 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 [...]

92. [...] Plain English explanation of VLOOKUP, OFFSET and MATCH formulas [...]

93. 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

94. [...] used: INDEX | OFFSET| INDIRECT | Array Formulas |  Using Date & Time in Excel Spread some love,It makes you [...]

95. 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

96. 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?

97. 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

• Scott says:

Chandoo,
Your solution for Ionel is almost exactly what I was looking for. My dilemma is the inclusion of multiple 0's in the data and the comparison is down a column rather than across rows.
My attempt to modify your example formula: AVERAGE(OFFSET(\$B3,,MATCH(MIN(ABS(B3:D3-C3:E3)),ABS(B3:D3-C3:E3),0)-1,1,2)) became: AVERAGE(OFFSET(\$E32,,MATCH(MIN(ABS(if(E32:E44>0,E32:E44)-if(E33:E45>0,e33:e45)),ABS(if(E32:E44>0,E32:E44)-if(E33:E45>0,e33:e45),0)-1,1,2)))).
I got a "too many arguments" message.
*Instead of adding another column as you did in the example I copied the first entry in a new row at the bottom of the column.
Any help is greatly appreciated!
Best regards,
Scott

98. Ionel says:

Dear Chandoo,

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

99. 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.

100. 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

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

102. 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.

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

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))

105. 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)))

106. 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)

107. 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 ??

108. 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)

109. 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.

110. MMMM says:

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

111. 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)

112. 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)

113. 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))

114. 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))

115. 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)))

116. 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.

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

118. 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!!

119. 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 ?

120. 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

121. Alison says:

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

122. 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.

123. Hui... says:

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

124. Santz says:

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

125. 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
Depending upon what You enter in D3 you will get appropriate answers

126. 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:

127. Prat says:

gr8 it works

128. 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.....

129. 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

130. TeamMP says:

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

131. Elliott Bailey says:

Thanks a lot mate! Loved the simple approach!

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

133. 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!

I know this is old, but I just wanted to reply. Your conclusion is incorrect. index match does not need to start on row 1! Rather, the first row in the match needs to be equivalent to the first row in the index!

In other words, your formula is wrong. It should have been:
=INDEX(A2:J50,MATCH(F2,E2:E50,0),9),0)

Notice how match and index both cover the range of rows 2 - 50.

134. [...] Introduction to VLOOKUP Formula & more [...]

135. 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.

136. [...] 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 [...]

137. Vinod says:

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

• Hui... says:

@Vinod
Can you give us an example?

138. 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

Regards
Anitha

139. 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.

140. 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.

141. [...] 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] [...]

142. semaema says:

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

143. SGK says:

hello,

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???

• 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

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

145. 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

146. 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

• Bonthond says:

rbe2windhoekcc.org.na

or

264811223921@mtcmobile.com.na

so that i can have a look and try to come up with a solution.

Regards

Bonthond

147. 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...

148. BoredGourd says:

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

149. 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

150. 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

151. 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

152. 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?

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

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```

153. 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

154. 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?

• 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 ?

155. 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?

156. 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

157. 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.
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.

158. 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?

159. 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!

160. [...] More on this technique, OFFSET formula explained [...]

161. 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?

162. 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

• John says:

Yes, use the \$ sign to freeze a reference. Use it in front of the row to freeze the row, and the column to freeze the column.

If you click on the range (which puts you in edit mode) and press the F4 key, you'll see that it applies the \$ sign to the row and column in the range. Press it again and watch what happens. Press again. And one more time. It changes where the \$ sign is placed. It's a handy shortcut.

163. 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?

164. kasusx says:

165. 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

166. 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!

167. SaifUllah says:

Very good help matters

168. Mohammed Usman says:

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

169. Massimiliano Matrale says:

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

Massimiliano

Homework
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

170. 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.

171. 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!!

• Hui... says:

@Nabil
Can you post the question in the Chandoo.org Forums
Please attach a sample file with some data
http://chandoo.org/forum/

Also state whether there are multiple entries per month per vendor or just a single entry

172. ApparentlyStupid says:

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

173. Bryce says:

Hi Chandoo, thanks for this excellent post.

Am a medical researcher in Australia, and am looking for a function with powers very similar to this. I have two datasets; one lists patients by ID number, and their In/ out time from hospital;
e.g (ID) 123456 (time in) 04/02/12 09:45 (time out) 04/02/12 18:02.

Many patients present repeatedly.

The other dataset is of a certain lab test, organised by patients ID number, time taken and the test value.
e.g. (ID) 126599 (time taken) 12/10/12 00:04 (value) 29

Most patients get more than one test, and I am only interested in the highest value per patient.

Hence, I am after a function that for each patient presentation, looks up the lab test table, matches the ID, matches the time, and of multiple possible lab results that meet these criteria, returns only the highest value.

A little complicated, eh?

174. manoj says:

Is there any way that we can vlook 2 or more sheets at a time

175. John says:

Please! You are hurting my eyes. "For eg." is NOT correct! The abbreviation, "e.g." (never with only ONE period!) is an abbreviation of the Latin exemplia gratia, or "for the sake of example."

To say, "For eg." is redundant!

The correct usage is simply, "E.g., OFFSET (A1, 3,4, 5,6) would ..."

• Chandoo says:

Thank you John for teaching this. I am not a native speaker of English. So I don't know many things. I will keep this in mind when writing future articles.

176. Kevin says:

Great tutorial Chandoo. I love your teaching style and your website is very helpful. I have seen many people use Excel as an electronic card-ex and search for data manually by scanning through the cells with their eyes ! It is hard to believe but it is so common to actually abuse Excel through lack of knowledge, time and ignorance or all three.
Your website is showing people that Excel can be their best friend in business not the villain.

177. Anil says:

Hi Guys

i Work of incident management so i track incidents on XLS and have to prepare reports ..i need to get 1,2,3 Tickets from A Cloumn which are mentioned in single row as this row consists of 4.5,6 which i want to exculde, i tried vlookup function but failed.. please help me with vlookup

178. Dougie says:

What's wrong with Crtl+F to find the information you're looking for?
e.g. Instead of finding an empty cell and then laboriously typing =VLOOKUP("936-174-5910",D5:G21,2,FALSE), I can just do Ctrl+F, and type in 936-174-5910. You may not even have to do the whole number to find it that way.
Also, If I'm looking for several numbers I'd have to either copy and paste the formula or edit it multiple times.
The only thing I can thank of, is if you have a formula in your sheet referencing a value in another sheet, trying to Ctrl+F for that value does not return any results. I have just tested this for VLOOKUP but the formula is also unable to locate the value.
So I dont really understand the point in VLOOKUP, or why it is so important?

The point is that I may need to use it in a formula. Formula's can't use Crtl+F.

Say for instance I'm running an ecommerce website and I've downloaded a list of transactions. This list tells me what products were purchased, but doesn't tell me the price paid by the users (dumb system, I know, but it's just an example). Now say that I have another table somewhere in my workbook that lists the price for each product. I can use vLookup on my downloaded list to append the price of each product without having to manually type it in each time.

179. yusuf says:

i have a table as below named table1, i need to make table2 and table3
somebody can help me?

table1
REF IDA IDB lvl
1 105 102 1
2 103 108 1
3 101 102 2
4 107 104 2
5 101 112 1
6 109 108 1
7 111 106 2
8 103 104 1
9 107 102 1
10 109 106 1

table2 table3
IDA REF IDB REF
101 3,5 102 1,3,9
103 2,8 104 4,8
105 1 106 7,10
107 4,9 108 2,6
109 6,1 110
111 7 112 5

180. abdulaziz says:

Hi chandoo

with reference to your home work on v lookup.
have i entered the formulas correct?
for
1Q: =VLOOKUP(G17,B5:E17,3,FALSE)
2Q:=VLOOKUP(IF(OR(D8>D9),"jamie","jackie"),B5:E17,3,FALSE)
3Q:=VLOOKUP("jagjit",B5:E17,3,FALSE)/(C12)
4Q:=VLOOKUP(G17,B5:E17,4,FALSE)/SUM(E5:E17)*(100)

thanks

181. Great, We were doing this manually till now. You just saved our hours of time

182. Wings Of Technology says:

There it is, this article saved me a lot of time. I rarely used VLOOKUP() in my day activities because of its complexity and you explained it very well.

183. Ankit Kumar saini says:

hi how can i partially hide a Email ID i need to keep private
example like abcdefgh@gmail.com to ab****gh@gmail.com

184. Bijay says:

Loan amount is 500000.
interest per Annam 36%
I have made loan Daily Calculation schedule
As per my schedule sheet it showed 180000 interest after one year
My requirement is after one year the interest amount 180000 should be capitalized automatic i.e now the total loan is 500000+180000=680000.
And if the borrower repaid the amount of the loan, first it must be settled capitalized amount(i.e. 180000) then settled interest than settled original Principal(500000).
Please help to solve the problem in excel or if provided me the formula set excel, it's far better.
Thank you

185. Nicolas Tapia says:

Hi Chandoo,

I just found your site ( 2 days ago) and have read many of your post, first of all thanks for all the great info!

I have a question and hope you could help me.

I have a territory code which can be seen by different sales rep (SR)(depending of the pillar). So a single territory code, let's say "terr1" could be seen by SR 1, from pillar A, SR 2, from pillar B, and so on.

Some of these SR are not yet hired, so I have a column with the values "on board" and "TBH".

The first table has 3 columns, column A "Territory", column b "SR Name Pillar A", Column c "SR Name Pillar B". I need to get column b and c (the names).

The second table has 4 columns. Each sales rep is in individual rows. The column A "Territory", Column b "SR Name", column c "Pillar", Column d "Hire status".

I have to make a conditional look up, but I am not quite sure of how. I need to look for the Territory code, then to ensure the Pillar that I am searching, and after that, ensuring that the SR is "on board", after all of before is true, I need the SR name (e.g. Joe).

I tried a formula like this:
=IF(AND(Hired_status="on board", pilar="A"),Vlookup(territory,data,2,0)
but it ony works in certain cases.

Could you help me with this?
I hope I made myself clear 🙂

Thanks a lot

Nicolas

186. Robin says:

The article is very informative.

Thank you

187. nisha says:

Its easy to understand...thanks to shared this educational article..

188. priya says:

hello,

189. Ghaz says:

Hello,

Best regards,

Ghaz Hassan

190. Md Meraj Ul Hassan says:

Why It is from D5 from G21???could some body plaese tell me?

191. Hello Sir,

How to calculate one column daily count without change previous count.
For example:
District Total Count Approved Pending Rejected

Dhule 50 20 30 0

I need solution total count 50+Approved +Pending+Rejected
count without changing 50+ daily count added in 50 (App+Pending+Rejected)

192. Rahul says: