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

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

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

Dont use VLOOKUP or HLOOKUP, instead use INDEX/MATCH

Name, Amt, Region

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

Sam

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.

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

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.

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!

Thanks a lot

this so easy and nice illustration for there very useful function

keep up the good work

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.

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.

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?

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.

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

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!

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

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.

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.

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

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?

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.

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

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

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

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!

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

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

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

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

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.

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.

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.

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

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.

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.

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!

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.

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!

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

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.

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

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.

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

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.

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

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

@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/

@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/

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

@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

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

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