Hello Folks.
I have a rather bad news for you. My internet service provider (TATA Photon) has mysteriously blocked chandoo.org. My site hasn’t been loading since Sunday. While I know it is up and running, I cannot see it unless I go thru a proxy server like kproxy.
It may take a couple of days for this to be resolved. I am using this time to take a break from blogging and read some books that have been waiting. I am almost done with Edward Tufte’s The Visual Display of Quantitative Information
and Walkenbach’s Excel 2010 Bible
. I will be reading Stephen Few’s Information Dashboard Design
and then Walkenbach’s Excel 2010 Power Programming
.
In the meanwhile I have a fun VLOOKUP contest for you.
All you need to do is, share a VLOOKUP trick or tip thru comments.
And the winner gets an iPod nano
. 8gb latest version.
Some rules to keep in mind:
- You have to post the entire tip in the comments. No links please.
- You can post as many tips as you want.
- You can post tips on VLOOKUP, INDEX – MATCH, OFFSET – MATCH, HLOOKUP, LOOKUP or lookups done using SUMPRODUCT
- Bonus karma & points for people making an example excel file & linking it along with the tip.
- Contest closes on October 12, End of day (Pacific time, why? because I love the big, blue ocean)
- Winner will be selected randomly.
Go!
Read these excellent articles explaining – VLOOKUP, OFFSET & MATCH, SUMPRODUCT.
PS: If you would like to sponsor this contest, just drop me an email at chandoo.d @ gmail.com
66 Responses to “Bad News & VLOOKUP Contest”
Use the last argument in vlookup as 1 or TRUE to get the closest match.. E.g. where it can be used - let's say you are looking to look up for stock prices on certain dates and say the stocks that you are trying to compare are from different geographies.. If the date that you are looking up is a non-trading day, vlookup with 0/FALSE as the last argument will give you #N/A, but if the data is sorted in an ascending manner and last argument in vlookup is 1/TRUE, it will give you the stock price of the previous trading day close.. 🙂
Beauty of sumproduct for array evaluation - it not only works on columns/rows, it works on both together as well... i.e. we may have been used to sumproduct array testing on only columnar fields, but it can do array evaluation on both columns and rows simultaneously in one formula.. Say your column headings are in B1:Z1 and row headings are in A2:A100 and the corresponding data in B2:Z100; You can write =sumproduct((B1:Z1=test)*(A2:A100=test)*B2:Z100) E.g. Column headings had months and row headings had employee IDs and the corresponding data was say work done.. Then you can use the above sumproduct to lookup a given employee's work done in a given month.. Also try using multiple such criteria and works beautifully..
But it will work only if output result in numeric value.
If your VLOOKUP is used multiple times, e.g. a column of VLOOKUP values in a table, always keep the column reference argument outside the formula.
e.g. B3 = VLOOKUP(A3,MyRange,$B1,0)
Then you can populate B1 with the correct column reference, which is especially useful if MyRange is a dynamic range.
{=INDEX(Quantity,MATCH(1,((Sheet1!$A2=OrderID)*($B2=ProductID)),0))}
Need to do a look up on 2 or more columns of data - use an array formula using Index and Match to get the job done.
In the spreadsheet at the attached URL I have used data from the sample NorthWind database, which mimicks a task I need to do every month. I receive a report which lists elements of orders which have not invoiced. I have to do a look up based not only on the order number, but also the part number to determine the value of the individual lines which have not invoiced.
The formula works by creating an array of OrderIDs and ProductIDs and if they both match the one being searched for, a value of 1 is returned, leading to the row number being obtained and finally returns the relevant information using the INDEX function.
{=INDEX(Quantity,MATCH(1,((Sheet1!$A2=OrderID)*($B2=ProductID)),0))}
In the example, I've named the different ranges to keep it more simple.
Don't forget, as it's an array formula, you'll need to hit CTRL+SHIFT+ENTER once you've finished editing the formula.
Look forward to some interesting posts here.
Regards
Chris
@Vipul - Your SUMPRODUCT example does not work in XL 2003 - throws a #VALUE error. Is this an improvement in 2007/2010?
Quick tip for VLOOKUP: Instead of typing FALSE or 0, simply leave it blank like so:
=VLOOKUP("Chandoo",A:C,2,)
Saves a few keystrokes at least.
Hadn't added a URL to this before - click my name, or the URL here for the Array Lookup Example spreadsheet in the above post.
http://cid-8699b7f2d0ad4dd9.office.live.com/view.aspx/.Public/Array%20Lookup%20Example.xls
Regards
Chris
I use INDEX - MATCH to find values for iterative life cycle cost calculations of renewable energy solutions. These values are then plugged into a standard format report that can be easily printed or saved to PDF. I'd link to these but I'm unsure if it's legal since they were made on company time.
Dynamic Range VLOOKUP:
One frustration I have with VLOOKUP is how static Table_array calls are in the function. Inevitably after you create and spreadsheet with many VLOOKUP calls, the main Table_array reference grows necessitating a redo of every VLOOKUP call in the workbook.
Here’s a tip to create dynamic Table_array references using the OFFSET function that allows your VLOOKUPs to grow with your Table_array references.
First, create a named range “data” that defines the range of data you wish to call on using the VLOOKUP function.
Names in workbook: data
Refers to: =OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
The count functions inside OFFSET allows the range to grow with the addition of rows or columns.
Then simply use the named range instead of using an absolute reference in the VLOOKUP function.
=VLOOKUP(D9,$A$2:$B$32,2,0)
becomes
=VLOOKUP(D9,data,2,0)
here's an example worksheet to see it in action: http://goo.gl/cmHa
I picked this one up off of MSDN.
One of the things that can big sets of vlookups is the constant swinging back and forth to your reference data to get column numbers. Either that, or you have to make like a table with some sort of driver (either named ranges, something that says your name column is 1, your month column is 2, your month column is 3).
So instead, I learned to use the column function which returns the column number for me:
=VLOOKUP(reference,testrange,COLUMN(B1),0)
It works with index/match too:
=INDEX(testrange,MATCH(reference2,A1:A3,0),COLUMN(B2))
You can even apply a -1 to the formula and use it for your offsets:
=OFFSET(A1,ROW(A3)-1,COLUMN(C1)-1)
Use ISERROR along with VLOOKUP to deal with instances where VLOOKUP does not find a match (returns #N/A):
=ISERROR(VLOOKUP(args),"No match")
You can replace "No match" with anything else, like a value or an empty string, for example.
It's going to be tough coming up with something new about vlookup!
I think it is good to remember that you can use vlookup within other functions. Or even use vlookup inside a vlookup, for example looking up a chart that decides which column to return.
I like to use multiple matching in my indexs like so:
=INDEX(MI_Agency_count_SEG,MATCH($E$223 & $O$226 & $O$227, MI_Agency_SEG & MI_Month_seg & MI_Agency_categorys_SEG,0))
You can use vlookups in conjunction with named ranges and column names to automatically select the correct column index from a table with the match function.
As long as your "field" names are the same in your reference table and your result table, you can find the column index using the match function, instead of hardcoding the information in the formula or another cell.
So for example, you could use:
=vlookup(reference,data_table,match(field name,index(data_table,1,0),0),false)
index(data_table,1,0) returns the entire first row of the data_table reference, and indexing that with your field name will give you the correct column number.
A real simple use of VLOOKUP (and a variation on snazzypants comment) that I use regularly is to identify items in a list that feature on a second list. I normally use this to allow a client to, say, exclude particular codes from a report (often a pivot table).
If the client enters the excluded codes in a single column range entitled "excludecodes" on a separate sheet, then:
In row 2 of the main data table, where column G includes the codes,
=IF(ISERROR(VLOOKUP($G2,excludecodes,1,false)),"Include","Exclude")
returns Include if the code for that row is not on the exclude list and Exclude otherwise.
This column can the be used to filter the data in a pivot table report.
I nest MATCH within VLOOKUP so I do not need to name cells/ranges nor count the number of columns for the VLOOKUP. For example, to lookup March Sales data:
=VLOOKUP($A2,Sales!$A$1:$M$25,4,FALSE) becomes
=VLOOKUP($A2,Sales!$A$1:$M$25,MATCH(D$1,Sales!$1:$1,1),FALSE)
I find this combo very helpful and quick in defeating office wise-guys who insert columns, resort, and all other manner of mayhem where I would otherwise have to re-verify the VLOOKUP formulas including recounting the number of columns.
(And yes, you can achieve the same result with several different methods I just prefer the visibility of this one.)
could you pls provide with a simplex example of your above explanation.
To elaborate using the formulas above, say...
$A2 = Project # "12345"
Sales!$A$1:$M$25 = data table with Project number in Column A and Jan-Dec in Columns B-M
Column 4 (in VLOOKUP formula) = "04/30/13" (Apr)
My favorite trick is helpful when pulling many columns of data from one sheet into another and can be used with either LOOKUPs or INDEX. It is easiest to explain in an example:
Assume that in both your working spreadsheet and the reference spreadsheet (which you are importing from) have Column A as your reference data (and have the same header) and Row 2 as all of your data headers. You are pulling the information from Columns C though K of the reference sheet into the working sheet starting in Column E.
In your working sheet:
Set A1 = MATCH($A$2,'Ref_Sheet'!$2:$2,0) to allow flexibility if the Column for your reference data changes in your Reference Sheet
Set E1 = $A$1+2 since you want to start importing with Column C (2 over from your reference column)
Set F1 = E$1 + 1
Drag F1 to the right for as many columns as needed (in this case to Column M)
Set E2 = VLOOKUP($A2,'Ref_Sheet'!$A$2:$K$1000,E$1,0)
Drag E2 across as far as needed (again, in this case to M)
Drag E2:M2 down as far as needed (or double click the bottom right corner of M2)
Now all your VLOOKUPs have been created to pull multiple columns per reference value with minimal formula writing.
If needed, you could change the starting column by adjusting the formula in E1 to =A1+ the number of columns between your reference column and the first column you want to pull. You could also skip a column by adjusting the corresponding cell in Row 1 to = the previous cell + 2 (instead of +1).
Hope this helps as I know it has saved me loads of time over the years.
In the following example a builders merchant is offering discount on large orders.
The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the Total.
All the calculations take place in the Orders Table.
The name of the Item is typed in column C of the Orders Table.
The Unit Cost of the item is then looked up in the Unit Cost Table.
The FALSE option has been used at the end of the function to indicate that the product
names down the side of the Unit Cost Table are not sorted.
Using the FALSE option forces the function to search for an exact match. If a match is
not found, the function will produce an error.
=VLOOKUP(C126,C114:D116,2,FALSE)
The discount is then looked up in the Discount Table
If the Quantity Ordered matches a value at the side of the Discount Table the =VLOOKUP will
look across to find the correct discount.
The TRUE option has been used at the end of the function to indicate that the values
down the side of the Discount Table are sorted.
Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does
not match a value at the side of the Discount Table, the next lowest value is used.
Trying to match an order of 125 will drop down to 100, and the discount from
the 100 row is used.
=VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1,TRUE)
Discount Table
Unit Cost Table Brick Wood Glass
Brick £2 1 0% 0% 0%
Wood £1 100 6% 3% 12%
Glass £3 300 8% 5% 15%
Orders Table
Item Units Unit Cost Discount Total
Brick 100 £2 6% £188
Wood 200 £1 3% £194
Glass 150 £3 12% £396
Brick 225 £2 6% £423
Wood 50 £1 0% £50
Glass 500 £3 15% £1,275
I have stopped using VLOOKUP and INDEX a long time back when I discovered that MATCH and OFFSET can do what I want. The problem I faced with the first 2 was that of the range which had to be changed when I added additional rows of data, mainly price data.
With an exact MATCH, I always find the the row or column for unique values and using OFFSET i can extract single cell data or feed a range data into a dynamic range name which can be used for charting and further analysis.
@Luke M re Vipul's comment
XL 2003 works for sumproduct formula. I tried this SUMPRODUCT((B1:F1="test")*(A2:A4="jill")*(B2:F4)) and got the value.
One of my favorite things for vlookups is to use wildcards within them. Basic formula is, as follows:
=Vlookup("*string*",Table,Col#,0)
or with a cell lookup:
=Vlookup("*"&A1&"*",Table,Col#,0)
It isn't case sensitive and it will search for a text string within words (e.g. "set" will be found in "offset"). It will also return the 1st instance of a string if multiple instances are found and you must use exact in the range lookup.
Link to File
http://cid-a0e1b3262186c132.office.live.com/view.aspx/Public/Wildcard%20Vlookup.xlsx
Helper column:
[Reference]&concatenate(countif([reference column],reference,row)
Will automatically number multiple instances in the same list.
So:
Guns
Butter
Guns
Butter
becomes
Guns1
Butter1
Guns2
Butter2
Use this as a reference in a vlookup to return multiple values.
Let's say we have a table of data in I1:L5 that looks a little like this;
TableDataone two three
red 40 400 4000
green 50 500 5000
yellow 60 600 6000
blue 70 700 7000
Define some named ranges as follows;
TopCell =Sheet1!$I$1
TableCols =COUNTA(TopCell:OFFSET(TopCell,0,16384-COLUMN(TopCell)))
TableRows =COUNTA(TopCell:OFFSET(TopCell,1048576-ROW(TopCell),0))
TableData =OFFSET(TopCell,0,0,TableRows,TableCols)
TableHeader =OFFSET(TopCell,0,0,1,TableCols)
Then enter yellow in A1, two in A2.
And use a VLOOKUP to find the right value
=VLOOKUP(A1,TableData,MATCH(A2,TableHeader,0),FALSE)
All is dynamic as long as TableData is tle last object in the sheet (i.e. nothing below and to the right of it).
@Prem Thanks for reconfirming
@ Luke It should work even in 2003; not sure of your data layout and formula.. feel free to post a link to the file and will check it for you
@ All Luke's trick is quite of leaving a blank for last argument works well, but be sure to end it with the comma before closing the vlookup bracket; else it will assume the last argument to be one and give the closest match..
@Prem Thanks for reconfirming
@ Luke It should work even in 2003; not sure of your data layout and formula.. feel free to post a link to the file and will check it for you
@ All Luke's trick of leaving a blank for last argument works well, but be sure to end it with the comma before closing the vlookup bracket; else it will assume the last argument to be one and give the closest match..
@ Luke you have to make sure that the data array should be a matrix/table of exactly the (number of columns in the column evaluation, number of rows in the row evaluation)
Vlookup-Multiple-Choices
Vlookup Function in Excel does not allow you to search value on multiple columns criteria, however by using a simple trick as shown below
you can have multiple options to get the right data 🙂
The Idea is to create a new column that combines two or more columns data into one (depending on the criteria we want).
Steps:
For Example we want to know the Salary of Ram in month of February from data below
Name Month Salary
Ram Jan 3000
Shyam Feb 1300
Sunil Feb 4000
Ram Feb 2000
Subash Jan 1400
Reddy Feb 2500
Subash Mar 1900
Using Vlookup we can use criteria "Ram" or "Feb" but not both.
The Trick : We will use CONCATENATE function and then Vlookup
We will add a new column in beginning and use formula=CONCATENATE(name cell,month cell).
We will have something like:
NameMonth Name Month Salary
And now running Vlookup with first column (NameMonth) we can have multiple criteria in place 🙂
See attached file for working.
http://cid-3fed9338d056e386.office.live.com/browse.aspx/Excel?Bsrc=EMSHOO&Bpub=SN.Notifications
Regards
Rohit1409
Regards
Rohit1409
I agree with a lot of the comments made so far on VLOOKUP ,,, many of us who have become more advanced, thanks to Chandoo and a few other Excel guru's out there in the big wide world only use VLOOKUP now and again, but I think it's important that many of the new users or infrequent users of Excel learn about the many iterations of using VLOOKUP, HLOOKUP etc, so I went back to an old file I had created when my company was beginning to migrate data from Lotus 1-2-3 ( yeah I know ) to Excel, one of my colleagues and I created a Help File which we shared amonst others within the company to make the transfer of data a lot smoother and one of the most widely used functions at that time was a SUM with VLOOKUP ... so here goes, Cells A1:E7 contain the raw data ( obviously a shortened version of real data ), col headings are Product, Model, Sales mth1, Sales mth2, Sales mth3, client wants to be able to quickly return the total Sales for those mth's by changing the model number within a cell in another tab ( F2 ) ..... formula to do this is =SUM(VLOOKUP(F2,Sheet1!B2:E7,{2,3,4},0)) and is an ARRAY formula ,, so Ctrl+Shift+Enter.
Indirect and Vlookup can be used to create dependent drop down lists. Sample attached.
My tip is one for use with VBA.
When adding a column of vlookups, you can solve everything in one shot if you use the R1C1 notation and a function that finds where your columns are. I use a helper function to MATCH column headers (so they can move around) and assign those integers to variables, and then do the same for the last row and column [cheating by using ActiveSheet.Range("A1").CurrentRegion].
With that, I populate an entire column with VLOOKUP formulas that all look at the right stuff to make things happen. (In the example case, the lookup range is also dynamicly named, and thus a variable).
Range(Cells(2, lastcol), Cells(lastrow, lastcol)).FormulaR1C1 = VLOOKUP(RC[" & useridcol - lastcol & "], " & LookUpRangeName & ", 1, FALSE)
click on the formula bar (fx) to get started and use the wizard
To return whether the value you are looking up is in the other dataset or not I use =NOT(ISERROR(VLOOKUP(...))). That way I get "TRUE" if it the value is present and "FALSE" if it is not.
@Vipul
Ah, I figured out what was going wrong. This syntax fails:
=SUMPRODUCT(--(B1:C1="Jill"),--(A2:A3="A"),(B2:C3))
This syntax works:
=SUMPRODUCT((B1:C1="Jill")*(A2:A3="A")*(B2:C3))
As my habbit is to use the double negative, I was not getting the right answer. Now, as to why once works and one doesnt...not sure.
Someone may have mentioned it already, but I also like doing a MATCH function (for the column arguement) on the column headers of my data table so I can dynamically change which field I want to return.
This i'm sure should be a familiar way of using V lookup in conjuction with a conditional IF
=IF(ISERROR(IF(C2VLOOKUP(B2,PreviousTblEmpPayrollListRange,2,FALSE),"CHECK!","OK")),"N/A",IF(C2VLOOKUP(B2,PreviousTblEmpPayrollListRange,2,FALSE),"CHECK!","OK"))
Previous entry was pasted incomplete as stated previously vlookup with conditional IF ...
=IF(ISERROR(IF(C2VLOOKUP(B2,PreviousTblEmpPayrollListRange,2,FALSE),"CHECK!","OK")),"N/A",
IF(C2VLOOKUP(B2,PreviousTblEmpPayrollListRange,2,FALSE),"CHECK!","OK"))
INDEX+MATCH Kicks VLookUp
VLOOKUP is useful when the data is arranged so that we look up for value in the left most coulmn. But what if we want Left Column
Values as result by seeing any other column value. VlookUp Fails to do so sadly 🙁
But we have a Trick around to do this, very well with the help of INDEX+MATCH functions 🙂
MATCH returns the position of a value in a range INDEX returns the value of a cell that is offset from another.
Combining these to power functions, we can use INDEX+MATCH to VLOOKUP, and to do the reverse of VLookUp also 🙂
See attached file for the Example
http://cid-3fed9338d056e386.office.live.com/self.aspx/Excel/RC-IndexMatch-Kicks-VlookUp.xls
Regards
Rohit1409
Picking up comment number 4: Here is an array-free version to perform a lookup based on two criteria, without using a helper column:
Return the value from column C where input in X1 matches column A and input in X2 matches column B:
=index(C1:C100,match(X1&X2,index(A1:A100&B1:B100,0),0))
Just enter normally, no need for Ctrl-Shift-Enter.
cheers, teylyn
@Rohit1409
I agree totally with you. I have stopped using VLOOKUP a long time back. These lengthy formula have been replaced by simple logic using MATCH AND OFFSET (with COUNTA and dynamic range names) to perform any of the complex operations. These two formula have been the basis of all my models which really amaze people around me. I feel they work faster than VLOOKUP and much easier to manipulate. I think if I have some time I will try to give the equivalent of some of the complex VLOOKUP formulas using MATCH and OFFSET
Prem
Using indirect() to get the table_array in vlookup() is a favorite when trying to use table_arrays from different sheets to bring in some data into one sheet..
@Vipul
Only drawback is the volatility of INDIRECT, which is an issue if there are many instances of it. My solution is to use CHOOSE to determine at which table to point VLOOKUP.
@Yard
Not really if your indirect is concatenated with the sheet name and cell names, it works similar to choose.. Moreover choose does not work on an array, you got to put the choices separated by comma which is painful in a longer set.
I like sum of multiple lookup results where in the lookup values are in an array.. E.g. My A2:B10 has my table array and say D1:D3 has lookup values that need to be looked from A2:A10 and the corresponding values is column B need to be added. Formula is =SUMPRODUCT(ISNUMBER(MATCH(A2:A10,D1:D3,0))*B2:B10)
To avoid showing error , People uses below formula...This Formula calls VLOOKUP twice, this means double processing.
=IF(ISERROR(VLOOKUP(A2,$H$18:$I$21,2,FALSE)), "Not found",VLOOKUP(A2,$H$18:$I$21,2,FALSE))
Avoid losing time when you work with intensive computing spreadsheets.
Use IFERROR instead…
Embed the VLOOKUP Formula into an IFERROR function. The syntax is IFERROR(value,value_if_error). The Formula is done:
=IFERROR(VLOOKUP(A2,$H$18:$I$21,2,FALSE),"Not Found")
I hope it helps . 🙂
Hi Ayush, the simple ones are the best ones, I like it....!
Thank You Bigtaff 🙂
I like using the multiple vlookups or hlookups together to create a more dynamic field. For example, I would use something like =vlookup(A5,DataRange,hlookup(A$2,DataRangeLU,2,false),false)
The Hlookup in the above example will dynamically identify which column from the DataRangeLU named range. The DataRangeLU is a quick named range section that easily counts the # of columns (or rows if using vlookup).
Exact lookups are very compute-intensive. If you have a lot of data rows, the lookups can be very slow. If I'm working on larger sheets, I (a) turn off manual recalculation, fill the destination column with placeholder "xxxx" so it doesn't calculate when the column autofills, enter the formula into the first cell, fix the inevitable errors, copy it down the column, then save. What about calculating, then? Shouldn't I calc *then* save. No. Excel recalculates before it saves. This way, if I've got something that's going to run for an hour or more (e.g. lookups from a 90,000 row file to a 900,000 row file), I can start it at the end of the day and walk away without worring that it's sitting there unsaved overnight. After it's calculated and saved I copy and paste values to get rid of the formulae.
HOWEVER. It turns out that 2 approximate lookups is way quicker than 1 exact lookup. So... put an approximate match lookup into an IF, and check against your lookup value. If it's equal (i.e. that it found an exact match), do the lookup again (still approximate) and pickup the column you actually want.
=IF(VLOOKUP(Absence[[#This Row],[ABID]],Enroll[ID],1,TRUE)=Absence[[#This Row],[ABID]],VLOOKUP(Absence[[#This Row],[ABID]],Enroll[#Data],COLUMN(Enroll[[#Headers],[GR]]),TRUE),"No match")
While creting a dashbaord I've to use the VLOOKUP most of the times and because in dashboards most of the metric are two dimesion metric (at least in my case) therefore instead of using hlookup inside to vlookup (becuase it unnecessarily slow down the speed) therefore I use the power of vlookup and little bit of data massaging to achieve this.
For Example:
Recently I created a dasbhoard for cross training status where Trainings were at top on X Axis and All the participants were on Y Axis. There were around 20 trainings and 15 participants and I need to show their status that whether they are "E" - Expert, "N" - Novice, "I" - Intermediate in a particular training. All that information in one page.
To achieve this I massaged the data little bit. So What I did was I created another column in raw data sheet joining the name of the participant and training and vlookuped that column by combining the participant and training name in the metric and fetch the status accrodingly.
Real time formula "=IF(ISNA(VLOOKUP($B6&N$3,Training_Data!$F$2:$K$82,6,0)),"",VLOOKUP($B6&N$3,Training_Data!$F$2:$K$82,6,0))"
It helped my team leads and department head a lot becuase in just one view they can see about the expertise of people and cross tarining happened in the department.
Thanks.
-Abhishek
I used the Vlookup to consolidate 2 sheets data to one summary sheet.
In my eg. I have two sheets for different class & Each class has different students. In the Master sheet I used the vlookup formula to get the consolidation of those 2 class sheets. I used two ansers in two versions: in 2007(IFError) and 2003 version(ISerror).
The link for the file:
https://docs.google.com/leaf?id=0B-MlqJ7Xb3vAYjk3NWJkYzEtOTgwNi00Y2UwLThkNDktZTFjMGRjNjBlNDVm&sort=name&layout=list&num=50
A good "advanced" example of INDEX +MATCH is to use the SMALL function with them. Let's say you have raw data which is multiple rows and columns and is just a sea of numbers and texts and you only want to see specific data in a separate worksheet ( say based on a country or a product ). The raw data contains many instances of that country or product and you want to be able to retrieve the data every instance that country or product appears within the raw data ,, one way to do this ( and it does look cool to management ) is to use SMALL + INDEX + MATCH. In the blank worksheet, enter the country or product you want to retrieve data for in cell A1, now in the worksheet with the raw data add a column at the left hand side ( column A ) and use this simple If statement ............=IF(B1="","",IF(B1=Sheet1!$a$1,ROW(),"")) ...... this references the country or product you want to retrieve the data for, copy this down the entire length of column A ( you will notice that every instance of the data in cell A1 of Sheet1 is given a row number, this I call frequency. In the Blank worksheet ( Sheet1 ) type this formula in cell A2 .... =IF(ISERROR(SMALL(Sheet2!$A:$A,ROWS($a$1:a1))),"",
INDEX(Sheet2!b:b,MATCH(SMALL(Sheet2!$A:$A,
ROWS($a$1:a1)),Sheet2!$A:$A,0))) ...... the INDEX MATCH argument ( Sheet2!b:b returns the data from column B from the raw data ..... you can then move this to return what ever data you wish from the raw data ,,, doesn't need to be ALL the data ,, you can skip columns. Let me know what you guys think.
VLookup-Syntax: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
If range_lookup is TRUE or omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value.
In my example I assume that table_array has 1 row header. To check if the first column of table_array is sorted, create a named range called IsSorted and add the following in the "RefersTo": =AND(OFFSET(table_array,1,0,ROWS(table_array)-2,1)<OFFSET(table_array,2,0,ROWS(table_array)-2,1))
You can then use this newly created name to display a message if the data is not sorted and prevent VLOOKUP from returning incorrect results:
=IF(IsSorted,VLOOKUP(....),"The first column of your data has to placed in ascending sort order!")
Correction: table_array has 1 COLUMN header (not ROW header)
Sorry,
Dominik.
I attached the workbook, which shows how to do Multiple lookup in multiple ways. I used vlookup, Match index, sumproduct and Array formula - for the same example. Let me know if you have questions.
http://cid-9fbb85221c1cb69c.office.live.com/self.aspx/public/MultipleLookup-multipleways.xlsx
Regards,
Vinod.
Hello Vinod,
couldn't found the excel sheet as given in the link. could you pls help?
Rgds,
Jim
Drag the vlookup formula for multiple columns look-up and no need to manually input the column reference & return #NA to zero or text
1. In a row, indicate the column number starting from first column of the table or array
2. In vlookup formula, you will need to lock/put $ sign in some column and row, so that when you drag the formula the lock coln or row will not change
"Formula VLOOKUP($A15,$B$4:$M$10,B$2,FALSE)
where $A -pertains to the lookup column reference or can be text let say you want to look-up for the value of Product 4
where B$2 - pertains to the column of value to return if it matched the look-up value
Example: A3 to M3 - column header A4 to M10 - data array/table then in A2 to M2 - indicate the column number (1 to 13)
A15 to A17 - lookup value reference
3. For #NA, you can just simply add additional formula in your lookup
Formula Return the #N/A to 0; IF(ISERROR(VLOOKUP($A15,$B$3:M$10,B$2,FALSE)),0,VLOOKUP($A15,$B$3:M$10,B$2,FALSE))
Formula Return the #N/A to TEXT:
IF(ISERROR(VLOOKUP$A15,$B$3:M$10,B$2,FALSE)),"None",VLOOKUP($A15,$B$3:M$10,B$2,FALSE)) - enclosed in " " the text you want to return for #NA
I guess there is little to add to this excellent source of tips, but let me share my small tips. They may have already been mentioned, but anyway. a) In VLOOKUP, I use 1 and 0 instead of "True" and "False" - much quicker. b) whenever possible, I use SUMIF instead of VLOOKUP. I usually work with numbers, and SUMIF returns 0 for missing lookup, which in VLOOKUP needs to be solved via error functions. When error solution for VLOOKUP (or any other similar function) is required, IFERROR is much better than IF(ISERROR(lookup)), especially in case of many populated cells (I am not sure but may be available in 2007+ only). Wildcards are very nice enhancement to all lookup formulas - on the other hand, if data formatted appropriately, consider applying auto filter. Excel 2007+ offers nice filtering options which may be faster than composing lengthy formula. Same to be said about pivot tables - great in situations where the looked up values are dynamically changing along the data analysis.
WOW!!!
Thank you so much everyone. There is such incredible wealth of information in this post.
The contest is closed now. I will be declaring a winner in next few days. However, Please continue to share your knowledge
Ok... the winner is dan l... congratulations.. 🙂
Thank you everyone for being so generous and sharing your ideas and tips 🙂
[...] Prem, Rohit1409, John, Godzilla, Chris Byham, judgepax – Please click on their names to learn even more. [...]
[...] Rohit1409, dan l, John, Godzilla, Vinod [...]
[...] Vipul, Ayush Jain, Spotpuff, Glen Feechan, Dominik Petri, Lukas for their valuable tips & ideas. Click on their names to learn more on using VLOOKUP. [...]
[...] John for the tip. (Click on the name to see tip) [...]
[...] Spotpuff, judgepax, Bryan for the tip. (Click on the name to see their [...]
Dear,
I have 3 column 1st is Name, 2nd is month and 3rd is Chq No. and need data as follow
Name | MonthA | MonthB | Month C
x Chq No Chq No Chq
y Chq No Chq No Chq
z Chq No Chq No Chq
How to get the information in such format..