VLOOKUP is a powerful and much-used Excel function. If you’re not familiar, it finds a lookup value in a table and returns the corresponding value of another column. This is great for looking up information by reference. In the example image above, Name is the lookup value, and it returns the matching Salary. If you are a power Excel user, you are likely very familiar with this function. If not, here’s how it works:

- =VLOOKUP(
*Lookup_value*,*Table_array*,*Col_index_num, Range_lookup*))**Lookup_value –**The value you want to find in the lookup value column**Lookup_value –**The table range containing columns for both the lookup and return values**Col_index_num –**The index number for the column containing the return values**Range_lookup –**The type of match: Nearest Less Than (TRUE), or Exact (FALSE)*[optional]*

VLOOKUP, though powerful and useful, is not without its limitations. In fact, it can be dangerous because of some unexpected effects and behaviors. If you use VLOOKUP, please read on to learn about these issues. This equally applies to VLOOKUP’s sister function HLOOKUP (which does horizontal lookups instead of vertical lookups) and another similar function, LOOKUP. I have provided a safer and more flexible alternative lookup method, which I use virtually everywhere in place of VLOOKUP.

First, I’ll explain some of VLOOKUP’s limitations:

*Your data range is limited to a table*. That means the data you are looking up has to be in a standard tabular form. You cannot use VLOOKUP to find a lookup value in a different table, sheet, or offset row. This limits the ways you can display your data, as anything you want to lookup must be available in a standard table format in your spreadsheet.*VLOOKUP always searches the leftmost column of the specified table to find the lookup value.*Again, this limits your choices in presenting data as lookup values always have to be to the left of the return values. This sometimes means you must have multiple copies of tables in order, think far ahead when creating tables that might be used in lookup, or reorder columns after the fact simply to use VLOOKUP.*You can only specify the return value column by index number.*This means there is no way to include a static reference to the return value column. If someone adds a column between the lookup value column and return value column, it will break your VLOOKUP and you have to manually increase the column index number in the formulas. This is a maintenance nightmare.*VLOOKUP provides a very limited approximate match feature*. The only aproximate match option finds the nearest “less than” value. Unless you want that type of behavior, you’re out of luck and can’t use it.

Now, here are some dangers of VLOOKUP:

*By default, VLOOKUP uses approximate match.*If this is how you want it to function, then great… However, in many cases you want an exact value returned. It gives no indication it is picking a closest match result. If you do not want this behavior (which is most of the time, I have found…), you remember to explicitly set the Range_lookup argument in the formula to FALSE. Range_lookup is optional, and not a very descrive name of this feature, so it is often overlooked. This quirk is exasperated by the second danger…*VLOOKUP can provide false results if the table is not sorted in ascending order!*This is an issue when you use the approximate match feature, which is TRUE by default. Basically, VLOOKUP starts at the top of the table and goes down row by row until to gets to a valie less than or equal to the lookup value. If your table is not sorted in ascending order, this can give false results, as the formula stops processing rows immediately after finding a “match.”

Sufficiently scared? Checking your spreadsheets for issues now?? The answer to these problems and limitations is the INDEX-MATCH lookup method. This methods uses two functions together to provide a more safe and flexible lookup feature. Here’s how each function works, independently:

- INDEX returns the value at the intersection of a row and column in a given range.
**Formula:**=INDEX(*Array*,*Row_num*,*Column_num*)**Array –**The range of cells**Row_num –**The row to return data from**Column_num –**The column to return the data from*[optional]*

- MATCH returns a position of an item in an array that matches a value.
**Formula:**=MATCH(*Lookup_value*,*Lookup_array*,*Match_type*)**Lookup_value –**The value you want to find in the lookup value array**Lookup_array –**The range containing lookup values**Match_type –**Exact (0), Nearest Less Than (-1), or Nearest Greater Than (1)*[optional]*

Combining the two functions, you are able to effectively beat the limitations and dangers of VLOOKUP. You can build a lookup that allows you to specify the lookup column and return value column completely independently, and also control of the nature of the approximate match, if not exact. The arrays are ranges and you can specific Column_num in place of Row_num, which mean you are not limited to using columns only; this can be used in place of HLOOKUP. It still has the approximate match feature as nearest “less than” by default, and carries the same sort warnings as VLOOKUP. Here’s how the combined function works:

- =INDEX(
*Return_value_range*, MATCH(*Lookup_value*,*Lookup_value_range*,*Match_type*))**Return_value_range –**The range that holds the return values**Lookup_value –**The value you want to find in the lookup value array**Lookup_value_range –**The range containing lookup values**Match_type –**Exact (0), Nearest Greater Than (-1), or Nearest Less Than (1)

Enjoy, and use in good health!

*Example of a INDEX-MATCH formula exposed in an Excel spreadsheet*

DUDE! This saved my sanity! Thanks so much.

um, Vlookup(target,range,match(colHeader,TableHeaders,0),false)

What’s your problem with this?

Hello!

Thank you, I found this interesting, but isn’t your variant limited too? Ideally I would use “like” options not just exact, less, greater.

Any ideas?

Thanks!

Nikolay,

Yes, it is still limited. I believe you can use wildcards by appending asterisks (e.g., “*”&[VALUE]&”*”) on both sides of your lookup value. Give that a try, maybe it will help expand the scope to what you need. Best of luck!

Thanks,

Ben

Can you tell me if I can use vlookup, excel 2003, to search for results within another sheet or folder. This is driving me crazy.

Jim,

You can certainly reference another Sheet or another Excel file (which is what I think you mean by “folder”). Here’s how to format your references for those situations. These examples demonstrate selecting a table of cells, A1 to B4.

If cells are the the current sheet: A1:B4

If cells are in another sheet: SheetName!A1:B4

If cells are in another document, same folder: [filename.xslx]SheetName!A1:B4

If cells are in another document, other folder: ‘C:\path\to\file\[filename.xslx]SheetName’!A1:B4

You can use that format for any of the references in VLOOKUP or INDEX-MATCH, or any formula for that matter.

Hope that helps!

Can you tell me Vlookup limitations

Hi, Santosh,perhaps this post would help you? http://thinketg.com/extended-vlookup-function-for-excel/

This is just nitpicking, but the file extension is .xlsx (eXceL Sheet X). This is not an issue when having both workbooks open and graphically selecting the ranges, but it might cause some headache for users typing the reference in manually, following the above example.

I often make this same typo when saving files and end up with “filename.xslx.xlsx”, which is probably the only reason I noticed it. =)

I appreciate the index-match method and will try it out, as I’ve had my fair share of vlookup workaround actions to deal with!

I dont seem to make it work if I am pulling information from multiple sheets. Is there a variant when using multiple tabs?

It should work fine for multiple sheets… See the comment above for Jim. Just remove the filename part.

Hi Ben

Please reply to my query. Through VLOOKUP I want take value of one element from different sheet. But that sheet has 3 types of values, So I need the highest value is the answer. How can i do that? Any other formula?

Regards

Joseph

Awesome. Worked exactly as described.

Thank you very much!

Great article. Exactly what I was looking for. Thanks

Hi, Ben. Very informative. Thanks.

Question though. I have a table with a column of Zip codes (cb2:cb71). These are the Zip Codes for a package PICKUP location. DELIVERY location Zip Codes are (cc1:dv2).

The charge for pickup and delivery (zip to zip) is in the table.

I have tried to use vlookup doing this, even index and match. I keep getting #N/A errors. How can I matchup the charge to the pu zip and del zip? Thanks, Rick

Rick, if you want to match on multiple columns like pickup and delivery ZIP codes, one way to do it is to concatenate each individual combined possibility into a single hidden column. Then, use the INDEX MATCH solution on that hidden column to pull your price from the table accordingly. You may have to reformat your sheet a little (since it sounds like you’ve got multiple delivery columns), or perhaps use a new tab in your workbook to do some table transformation behind the scenes. Does that make sense? There are number of other more complicated options as well, such as using a pivot table. Best of luck!

Great solution!

Ben for MVP!

Hi I am not very experienced in excel but I find that vlookup lets me choose the data array from another sheet whereas the index-match gives me N/A. Please advise. I am making a withholding excel sheet that computes everything itself once gross has been added. State taxes are in a table with greater than to less than equal to.

Amanda, there’s no reason why INDEX MATCH shouldn’t work across multiple sheets… There must be something else going on. If you can post some more details, perhaps I can help figure out what’s going on.

I’ve been trying to get this to work with my data for a while now – maybe you can give me a pointer. I am trying to reconcile certain transactions in my ledger to bank account transactions. I’ve made a Vlookup that is pretty lengthy, but works for about 80% of my straight-across matches (sometimes our GL lumps up two or more bank transactions as one journal entry). But the real problem is when I have multiple occurences of the same transaction amount, but multiple times throughout the month. I got a Match Index function to work with one test transaction, but then it doesn’t apply to the rest of my entries.

Here’s an example of the columns I’m working with. All of our GL entries are on the left side, and we have a separate tab that contains the bank transactions to match against.

Trans. Type | Reference | Date | Amount Amount | Date | Ref Number | D/C

AP | Check Paid | 15-Nov | 2500.00

Bank Data

Amount | Date | Ref Number | D/C

2500.00 | 3-Nov| etc..

2500.00 |12 -Nov| etc…

2500.00 | 20-Nov| etc…

2500.00 | 30-Nov| etc…

The tricky thing is that only first two columns of data that the bank provides will match anything we have in our GL. And the bank transactions may be a couple days, or even a couple weeks, away from the GL entry for the corresponding transaction.

Any ideas?

Josh,

Wow, that sounds like a tough situation. Looks like you don’t really have reliable or identifiable fields to do the match. Are there any other fields (reference number, etc.) you can use to match more reliably? If not, my only other through is to use some chronological magic and forego the dates entirely. Meaning, you can make column in your bank data that adds an date-based index (1, 2, 3, 4, etc.) for like amounts in the same month. Then, do the same on the GL data and match using the index so the exact date won’t have to match. That way, the first 2500.00 bank data entry can match with the first 2500.00 GL entry, and so on. Make sense? Not sure if it will work for you, but worth pondering perhaps.

Best of luck!

Hi Ben,

I am also somewhat inexperienced with Excel and need some help with either of these functions, whichever would best work. I have one sheet of customers and another generated by the post office with tracking numbers. I need the tracking numbers inserted corresponding to customer names on my own spreadsheet. I have tried vlookup and seem to always reach an error message. Thanks for your help!

Louisa,

The correct formula might look something like this:

=INDEX(PostOfficeSheet!B:B,MATCH(CustomerSheet!A1,PostOfficeSheet!A:A,0))

PostOfficeSheet is the name of the sheet with the PO data. Column A has the names, and column B has the tracking numbers.

CustomerSheet is the name of the sheet with the Customer data. Column A has the names, and put the above formula in column B to get the tracking number.

Hope that points you more in the right direction!

Awesome…. this was driving me mental…. thank you very much

I believe this is helping me head in the right direction but I still cannot get the desired results.

What I am trying to do:

I am currently working a man-hours sheet of my field workers (50+ workers), their job class’ vary (ie. LBR 1, LB 2,…LBR 6, 45% CARP, 80% CARP, JT CARP, MASON, PLASTER MASON ETC) I am tasked with attempting to sum man hours daily by class ie laborer (LBR 1-LBR 6) Carpenters, Masons etc.

I would LIKE to be able to search partial text of job class like “lbr” so I can find ALL laborers in column A1 and have the correlating hours summed in B1. “Carpenters” A2 and Their total Hours B2 and so on. I’m ok with having a hidden list populate and I then sum and tidy up in a nice neat table later. I just cannot find a way to do ‘if *this cell* says “lbr” at all, display hours from *this cell* if not, don’t display’ I can drag and sum later.

Better yet! Since every “lbr”, for example, shares the row with the hours I’m concerned with, if I could search and sum EVERY lbr’s corresponding hours into 1 cell that would be excellent. By the end of this project I’m going to have 20 working classes and over 50 workers and I’ll need daily sums of each one.

Any direction would be appreciated!

Steven

Steven,

Sounds like you might want to try using the SUMIF function instead of INDEX-MATCH or VLOOKUP. As with both of the latter, you can also use wildcards in SUMIF to do partial matches.

Your formula might look something like this:

=SUMIF(DataSheet!A:A,”*”&SummarySheet!A1&”*”,DataSheet!B1)

DataSheet is the name of the sheet with the raw data. Column A has the labor code and column B has the hours worked.

SummarySheet is the name of the sheet with the summary data. Column A contains the full or partial labor code and column B has the formula above (to get the sum of hours worked).

Hope that gives you an idea of how to tackle this!

Ben,

SUMIF is definitely the way to go! Thank you, the use of wildcards and SUMIF has made this so much more simple. Happy new year!

Hi there! The Match_type parameters (-1) and (1) work the other way around don’t they? Also the sort order of the lookup values does matter if using these parameters to get the approximate match – you said there is no sorting requirement? I’m using Excel 2010..

Phil, good catches! You’re correct on both counts. I will have to update this posting to reflect those changes. Thanks!

Thanks Ben sorry it’s months later now but I notice the Match_type parameter is correct in the combined Index(..,Match()) function but is shown differently under the Match() function. Both should be something like Match_type – Exact (0), Nearest Greater Than (-1), or Nearest Less Than (1)

The Nearest Greater Than (-1) requires a descending sort order, while Nearest Less Than (1) requires an ascending sort order.

Sir,

My worksheet is about witholding tax on compensation … which, I am doing manually (very) … and as much as i would like to use v-lookup function, i am afraid i can’t … for i still have to learn how to use it … i can say , that i am very familiar with the basic excel … so, i a am confident that my work is correct … but, i wanted to improve my knowledge using advance excel,to minimize time in preparing the tax computation and finally, preparing the alphalist …

Please help …

jo db, you should be able to use the INDEX-MATCH formula I discuss in this post to effectively do the same thing as VLOOKUP… Is there somewhere in particular you are getting stuck? I can help if you provide a little more information on where you are having a problem!

Thank you for sharing this awesome tip! I recently mastered the VLOOKUP after trying to learn it for at least five years…It was the sorting that kept me thrown off course.

seriously, I will not use vlookup henceforth…index match is just perfect…

I am trying to extract the agent associated with a supervisor from a master file to the supervisors worksheet, I am accomplishing this by vlookup(supername, supernames,agent names,2,false) and similar index, match I have it working however it stops working after 5 agents, I got it working in vlookup but could duplicate the result on other supervisor pages, is my excel corrupt or is there a better way?

That sounds frustrating! Are you using absolute cell references appropriately, using the “$” in your formula’s ranges? Sometimes what you’re describing can happen when you copy a formula to other cells with non-absolute ranges. If that doesn’t work, give me some more details and perhaps I can help!

Question?? I have three spreadsheets. Currently using vlookup to fill in missing columns of data in first one, from 2nd & 3rd. Then, my dilemma. I am trying to sort the first sheet by numbers in one of those columns and ‘sort’ doesn’t recognize as number values and won’t give me “largest to smallest” as an option. Suggestions?

That sounds like a cell formatting issue, perhaps. Make sure the cells in that column are set as a numeric format and you should see the right sort options.

I have a question Vlookup only brings up the first match. but I want to all matches For example:

Part Number Address

G5S8 FR1

G5S8 TR1

vLOOKUP only finds the first one. what can I use to find both of them

VLOOKUP and INDEX-MATCH can only handle finding one match. You may need to rethink your strategy if you’re trying to find multiple… Maybe if you can explain more about what you’re trying to do I can help you figure it out!

Hi Ben, great article! After much googling this is the closest I’ve seen to getting this figured out (without having to learn VBA). I’m trying to create a column in an indentured bill of materials to identify the parent of each item. Basically it’s a parts list of an assembly made up of sub-assemblies and individual parts. The format is like below, column A identifies the depth of the line item and column B is the part number. So 0 would be the top assembly, all the 1’s would report to that assembly, and 2’s report to the previous 1, etc.

Level – Part# – Parent#

0 – Assembly1 –

1 – Sub-Assy1 – Assembly1

2 – Part A – Sub-Assy1

2 – Part B – Sub-Assy1

1 – Sub-Assy2 – Assembly1

2 – Part A – Sub-Assy2

2 – Part C – Sub-Assy2

The “Parent#” column above shows what I want the function to return. So starting in C3 (by Sub-assy1) I added

=INDEX($B$2:B3, MATCH(A3-1, $A$2:A2, 0))

and filled that down so the search range expands for each subsequent row. The problem is the function returns a value from the first match it finds. What I’m trying to do is find the closest previous row that is one digit less than the current one (in column A). For example, the very last row is at level 2, so the parent is going to be the previous level 1 (Sub-assy2). This function returns the first level 1 it finds and therefore puts the value Sub-Assy1 in the parent column instead.

Is there a way to have it search backwards starting at the bottom of the range? I tried reversing the order of the range and that didn’t make a difference. Or is there a better function or combination I should be using instead? I could create a row number column to use to find the smallest difference between the current one and all the rows with a match if there’s a way to do that.

Thanks for your help!

Thanks for all the detail! I think I have a grasp of what you are looking to do. Try substituting the LOOKUP function instead of using MATCH. If you use LOOKUP you can make it search from the bottom up. See details on how to use LOOKUP here: http://support.microsoft.com/kb/324986

Your example provides NOTHING more than what a VLOOKUP will do. The so called “danger” with VLOOKUP is not a danger when you use FALSE, or 0 (zero), at the end of the function, just like you have to do with MATCH portion of your INDEX. With either, if you do not put a zero at the end, then your result could give false readings or errors. So using the current example, the INDEX-MATCH is just as dangerous as VLOOKUP. By the way, doesn’t everyone use FALSE/0 at the end of the VLOOKUP function? I’ve seen tens of thousands VLOOKUP functions in my lifetime, and I have yet to see one with the TRUE/FALSE (1/0) missing at the end.

Anyway, using INDEX-MATCH is better than VLOOKUP because it can do more. For example, you can use INDEX-MATCH in cases where the lookup column is not the farthest left. VLOOKUP can only return the matching value from a table array where the lookup column is to the farthest left (and the matching value is in a column to the right of the lookup column). In your example, if cell E1 was “ABC Materials”, then you can get the person’s name by using:

“=INDEX(A2:A7,MATCH(E1,B2:B7,0))”

This will return “Rob Walters” as the result. You cannot do this with VLOOKUP since the Client column is to the right of the Name column.

Another advantage of INDEX-MATCH that you cannot do with VLOOKUP or HLOOKUP is that you can have the lookup data in a column and the matching value data in a row.

Also, you can use multiple criteria with INDEX-MATCH. If there were two people with the same name and several people per company, then you can do a lookup using both Name AND Company using INDEX-MATCH and entering the formula as an array.

Wow, you certainly know your stuff! I appreciate your comments and especially the additional advantages to using INDEX-MATCH. I’m sure readers will find that information very valuable.

There’s another major advantage to using Index-Match.

If you want to do multiple lookups over thousands of lines of data.

IE – if you had a table with 20 columns of data and you wanted to retrieve 5 of them, a common way is to use 5 VLOOKUPs (and that can easily be 10 or 15 or 20 if you’re doing any conditional lookups)- multiply that by 20,000 rows of data and you’re doing a lot of calculations.

Index/Match allows you to do one lookup (the row) and then reuse that for all of the other derived values. This can bring significant performance benefits with large datasets as the lookups are the slowest calculation, especially with unsorted data.

Hi Ben,

I have read through all the comments you have posted thus far and understood that both VLOOKUP and INDEX-MATCH is unable to return multiple matches.

On my side, what i am doing is basically using the MATCH (with wildcards) function coupled with the INDEX function as you mentioned earlier to search for possible matches in the same column (e.g. ApplePie, AppleTart) by just typing in ‘apple’ (search box in A2). As per what you said, how do I go about doing this besides using the VLOOKUP and the INDEX-MATCH functions? A brief simplified example is below (my actual database is on a different sheet from the search box in the same workbook but I can deal with the referencing)

Row/Col A B

3 ApplePie $3.00

4 BlueberryPie $3.00

5 StrawberryPie $3.00

6

7 AppleTart $4.50

8 BlueberryTart $4.50

Please help!

David

Check out this MSDN post about using an array formula to return multiple matches: http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx?redir=0#Return%20multiple%20corresponding

This is pretty complex! If you haven’t worked with array formulas, read carefully so you understand how to use it.

Hi Ben,

I have a column (A) with names, Column (B) with platoon numbers. On another sheet I have a table for each platoon and I want it to be populated with the names of each platoon indevidually. The names are alphabetically and the platoon numbers are mixed.

Column A Column B

Name Platoon

J Goodman 1

L Barns 7

H Law 1

B Aims 10

and so on….

I want all the names from platoon 1 to appear in the table for platoon 1. I have about 3000 members and 40 platoons.

Thanx

This sounds like the previous comment, where you want multiple matches. Check out this MSDN post about returning multiple matches: http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx?redir=0#Return%20multiple%20corresponding

Hi Ben,

I’m trying to get corresponding dates on an excel sheet containing different dates in the column A, when column B containing numerical values are sorted in ascending order, using the formula =IF(ISERROR(SMALL($A$2:$A$65536,K2)),””,SMALL($A$2:$A$65536,K2)). I have tried to integrate the formula above using Index and Match, but it’s not giving the corresponding dates. Any idea please

Are you using it as an array formula? If so, be sure you’re pressing CONTROL-SHIFT-ENTER after inputting or editing the formula. If you did it right, it will appear in {curly brackets} and you can copy it down to other cells.

Thanks for the help.

I am trying to make a spreadsheet to manage my expenses. I have categorized my expenses and i want to get all expenses of each category into a different table on a different sheet. I have tried to use vlook ups but it only returns the first expense under the category. Since each category has multiple corresponding amounts, how do i write a formula that i can copy down that will find each one?

If you want to match multiple corresponding amounts, you have to use an Array Formula, like the one in this example: http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx?redir=0#Return%20multiple%20corresponding

I want to look-up a value with multiple date criteria.

Sample Data

MOS 2 Jan 02 2 Jan 02 – 1 Jul 04

MM ST MM ST

15P 95 92

15T 105 102

So, if MOS = 15P and Test Date < 2 Jan 02 or Test Date is between 2 Jan 02 & 1 Jul 04 return ST score.

I don’t think vlookup is the answer and I am unsure how to write the formula.

Thanks

Sometimes in cases like this you can use the normal formula and turn off exact matches. With proper sorting, it might work as you want it to for ranges. Or, is your data more complex than that?

Thank you for the valuable information when I really needed it!

I have integrated this function into my VBA program but now I am searching for a way to tell if there is a match.

Do you know how to filter an error from the match part of the function?

Wow! Your way ahead of the tips in this article if you’re integrating into VBA code. Maybe if you explain a little more I can understand what you’re trying to accomplish and help out…

I have found a solution by using the “if error(match(….)) = True” function. It wasn’t that difficult to integrate the index-match function into VBA. In VBA you can recall every worksheet function by entering worksheetfunction.index(…..,match(….)). It works almost the same as using it in a cell. It works like a charm and it is really fast without having to use multiple loops to retrieve data and I don’t get the errors that I hade using the Vlookup funcion.

hi sir,

i have a doubt i need the same cell number data but different answer

eg,

vendor company name mobile no email id

26 NMC Razak 050-4796945 [email protected]

26 NMC mohb 050-5944956 [email protected]

26 NMC Ahmed 050-5137902

vendor no I need to get the result one by one

company

name 1

name 2

name3

mobile no

email id

pls note that there have lot of vendors in list please do with index and match functions pls help me

It sounds like you want to want to return data for all contacts based on a vendor number. To do this, you have to use an Array Formula in combination with a lookup strategy. I’m working on a blog post on how to do this. Please check back soon for a link to my next Excel post!

Hi Ben

I have read through this interesting thread as I’m struggling to generate the right formula to achieve something quite specific and wondered if you could point me in the right direction or assist me?

In column A I have a list of dates usually containing multiple rows of the same date.

In column B I have some specific details about an item, and for the sake of making this simple, let’s just say this column will either contain the text “a”, “b” or “c”

I would like to run a count in another sheet to look for today’s date in column A ( all occurrences of it as it will be in multiple rows) then check the corresponding value in the B column and total the results of B for each date.

For example if 04/04/2014 was in column A four times in separate rows and column B had two instances of “a” and one instance of “b” and one instance of “c” then I would like to show this data in the other sheet.

04/04/2014 A = 2 B = 1 C =1

Can you help please?

Thanks Tony

Hi Tony,

If I’m understanding correctly, I think you can use a few SUMIFS() functions to meet your needs. With SUMIFS() you can check multiple criteria to sum up numeric cells based on that. You will probably have to add a hidden column that stores the equivalent numeric values if the “item” cells are not numeric. Best of luck!

Thanks Ben

Is there any way to set up a formula across 4 columns? I need my spreadsheet to look at the part # and the date the RFQ was started, then go to the sales order spreadsheet and see if there is a matching part number with a sales order date that is greater than the RFQ date? End result would be that the spreadsheet would list the sales order# that was entered for that item that is greater than the date of the RFQ.

Wow, sounds complex! Try this: you can match multiple values by creating a hidden column and using & to join the two values together. Also you can use an & in your INDEX-MATCH formula similarly. Then, you can do a second lookup with the first matching value using a second nested INDEX-MATCH formula if you need two individual lookups to chain off each other. Make sense? Good luck!

I have a slightly different problem. I have a produced part that I want to look at the single items going into it, and copy an entire row based on the single items. It looks like this:

finish/part/description/process

I want to copy the entire row based on the individual part, as it is the only truly unique thing in the entire line. Is there a way to do this?

Thanks

Ted, sounds like you can accomplish what you need by using 3 INDEX-MATCH formulas–one for each column you want to bring over. Or, maybe I don’t quite understand. If that’s the case, add some more details and I will try to help!

I have a number that is in cell B1, I need to look for it on 5 separate spreadsheets, once found return what is in column 2 on that spreadsheet. What is the best way to go about doing this?

Kelli, you might try this: Write 5 separate INDEX-MATCH statements, one for each spreadsheet lookup. Then, nest them together using IF() statements, and use ISNA() to check if they return a match. This way, the first matching sheet will return the value, and otherwise try the next sheet until it goes through all of them.

Something like:

IF(ISNA([INDEX-MATCH 1]),[INDEX-MATCH 1],IF(ISNA([INDEX-MATCH 2], [INDEX-MATCH 2],IF(ISNA(… and so on.

Make sense? Good luck!

Ben,

I read this article and threads with interest concerning VLOOKUP and INDEX and MATCH. I use VLOOKUP at lot and have done for a long time, but I am having problems with some new spreadsheets that I have set up to help other people out. Both spreadsheets are on our server, but in different directories. The data source spreadsheet is rather large, nearly 10,000 rows and 12 columns, and is not usually opened. I have tried the INDEX and MATCH functions, but I get similar results although not the same.

When I use the VLOOKUP function, I get a lot of #N/A as well as some #REF! errors. When I use the INDEX and MATCH functions, I get the #REF! errors only and in totally different locations. However, in both situations, when I open the source spreadsheet, all the errors disappear and the retrieved values are correct.

On another note, when I close the source spreadsheet I get an error window “Excel cannot complete this task with available resources. Choose less data or close other applications.” I have tried the suggestions but nothing works.

Below are the formulas for both versions

=IF($B7″”,VLOOKUP($B7,’T:\Arrow\[Arrow.xls]Stocktake Sheets’!$B:$J,2,FALSE),””)

=IF($B7″”,INDEX(‘T:\Arrow\[Arrow.xls]Stocktake Sheets’!$C:$C,MATCH($B7,’T:\Arrow\[Arrow.xls]Stocktake Sheets’!$B:$B,0)),””)

Any suggestions for a function (even if I have to write one in VBA), that will act like MATCH but find the FIRST occurence (not largest that’s smaller than x) that meets a condition (like “>0”) in an array and returns the relative position?

I need “Date of last activity” where its listed by date:

04/27 04/26 04/25 04/24 04/23 04/22, etc…

0.0 0.0 0.0 6.3 0.0 0.0

I’d want a function that would return “4” so I could use INDEX to

return 24-Apr. The data could be anything > 0, 0 means no activity that day.

Doug, can you pre-sort your lists? If so, then you may be able to make it much easier by using Match Type to get the output you desire. Maybe give that a try?

index( , match()) is very good formula n got #n/a error in some part while using formula on my sheet,

Actually I have on excel sheet where I have given weightages to my activities which roll up on summary level, the content is copied from P6.7. while updating form other excel sheet with exact same format , for some of the value its ok but for others its giving me #n/a, i.e. match function gives #n/a, Need your kind assistance ,

When copying a formula, make sure your references came over correctly. It’s easy to forget to check those references and make sure they are absolute or relative appropriately! Good luck.

I am familiar with Vlookup; however, struggle to identify a value if the lookup value falls within a range. Example:

Lookup value = 5

Table array =

Table 1:

A B C

1 1

2 2

3 5

4 10

5 12

Table 2:

A B C

1 1 3 Alpha

2 4 6 Charlie

3 7 10 Foxtrot

4 11 15 Zulu

I need the result “Charlie” to appear. Here is how I do single value searches =if(isna(vlookup(A1,”table 2″$A$1:$C$4,3,0)),”Not Found”,vlookup(A1,”table 2″$A$1:$C$4,3,0))

Love to hear from the experts!

One thought is to sort the list and use then the Match Type value to find the nearest greater/less than value. Does that work in your situation?

I did, but may have written the index match wrong. How would you write it with example below?

Sheet 1

A B C

1 3

2 4

3 11

4 5

Sheet 2

A B C

1 1 3 Alpha

2 4 10 Charlie

3 11 16 Hotel

4 17 31 Zulu

I think that I was able to get it to work; as long as there are no gaps in ranges that would provide inaccurate (+1) results. Thank you.

Glad you figured it out, Bradley!

I am trying to do a simple Index/ Match (I don’t think VLOOKUP can do this) on a range of square footages with an associated price. I didn’t think this could be so hard!

I’m using two different sheets, one with all the data of the square footage pricing, and the other as a type of “reporting” worksheet. “Data Sheet” is where the prices are, and “Concrete” is what I’m working on. I have ranges of SF, and an associated price for that range.

Column S is the price, Column Q is the high end of the range (so -1 returns the value equal to or less than the high-end range)

Input into Concrete!D15 is this formula:

=INDEX(‘Data Lists’!$S17:$S57,MATCH(A15,’Data Lists’!$Q17:$Q57,-1))

It’s not bringing back the right data, and if you go above 500 on the reference cell, it gives an #NA error. Grrrr! This shouldn’t be that complicated!

I just tried to mix INDEX/ MATCH AND MATCH together, but I got a #N/A error… Here is the formula:

=INDEX(‘Data Lists’!S17:S57,(MATCH(Concrete!A15,’Data Lists’!Q17:Q57,1)*AND(MATCH(Concrete!A15,’Data Lists’!P17:P57,-1))))

CTRL-SHIFT-ENTER didn’t help either.

It looks like you might be using the AND() function incorrectly. It works like this AND(statement1, statement2, statement3) and so on. Each statement should produce a TRUE/FALSE value. Maybe you can explain more about what you’re trying to accomplish?

Are your columns sorted? One of the limitations of this is that they must be sorted appropriately for the formula to work with the match type you are using. Incorrect sorting might result in a #N/A being returned.

Hey, I’m trying to create a formula that searches for a cell value in

Spreadsheet Ain a set column inSpreadsheet B. If it finds the value it then displays“Found”and if it can’t find it“Not found”. How can I do this in Excel 2010? Would Vlookup or Index-Match be the way to go? Thank you for any advice you can give. ~RochelleI think I found a way to do it, at least in the one spread sheet. The formula is

=IF(ISERROR(MATCH(I13,G13:G20,FALSE)),”missing”,”found”)where I13 is the cell value to look for and G13:G20 is where to search.How do I go about writing a fomula that has

I13inSheet AandG13:G20inSheet Band then have theResultdisplayed inSheet 3? Thankyou :3I think I understand the question… Give this a try! If you want to reference other sheets, you can use the name of the sheet followed by an exclamation point in front of the cell reference. For example: Sheet1!G13:G20 will reference G13:G20 in the sheet named Sheet1.

If there are spaces in your sheet’s name, you have to enclose it in single quotes like this: ‘Spreadsheet A’!G13:G20

I’ve recently converted the majority of my vlookup uses to the Index-Match functions, some using arrays. However I’m currently having a problem. If I have a data set with Index-Match as a formula, and I sort that data, the cell reference on the Match function doesn’t update. With a Vlookup the cell reference would automatically update when the data is sorted. Any insight into this issue?

That sounds frustrating! Normally it should automatically update the formula results when you sort… This works the same in INDEX-MATCH as it does in VLOOKUP. I’m not sure what’s going on in your situation. Can you provide some more info that might help us figure this out?

Hello!

I am stuck with an issue that I’m sure needs a simple fix, but I am unable to find one! So hoping that you could shed some light on it.

I have a spreadsheet with 2 worksheets: WS1 and WS2

I have a series of values in WS2 spread across 3,465 rows and upto column ‘D’ (e.g cell D1897). I am trying to retrieve a value in worksheet WS1 (that could be in any column that I do not know)that corresponds to the value in cell D1897 of WS2. Please note that the rows in worksheet WS2 are column headers (row 1) in worksheet WS1.

Now, if I am aware of the column name in worksheet WS1, I can easily write a VLookup. However my problem is that currently I have to manually search for the column index number in worksheet WS1 inroder to complete the Vlookup. I was hoping if there is any method of automating this nested condition.

While manually doing the above, I use the following formula in an empty cell in worksheet WS2: =VLOOKUP(D1897,’WS1′!A:CAD,(COLUMN(‘WS1’!BYY:BYY)),FALSE).

Please note that in this formula, BYY is the column name that I have to manually find in order for the ‘Column’ function to return a value that I use as the column index number.

Sorry for the really long problem statement!

Thanks,

Vik

Hello! That’s a tough one, Vik. If you try to convert the VLOOKUP to an INDEX-MATCH function as described above, do you still have the same issue? Note that you will not have to do the same type of column lookup to make it work. I’m not sure that will solve your problem, but it might help you look at it from another angle. Best of luck!

Thanks a lot. This saved me from a lot of trouble.

Thank you, thank you, thank you. I was stumped for over an hour on #N/A using VLOOKUP…..changed the formula to Index/Match and it worked immediately. You’re a lifesaver!

Thank you so much for this. This makes my life SO much easier.

Thank you! I’ll live a couple of years longer just because of this!

I have been using Vlookup for many years and have only just found a problem. Users type in part of the search target description and then use the wildcard ‘ * ‘ and accept whatever value is generated – frequently wrong.

I just re-wrote the formula using INDEX & MATCH, with a tolerance of ‘O’ i.e. I want an exact match.

Unfortunately, the wildcard still ‘works’ in that it will generate a response that may or may not be correct.

Below is the formula, where I am searching for a match to cell H35, the data being resident on a Worksheet called ‘BASE’.

=INDEX(Base!$B$8:$B$875,MATCH(H35,Base!$A$8:$A$875,0))

Am I doing something wrong?

Sounds like a complex issue, David! Your formula looks fine… Are you wanting to remove the ability for them to use the wildcard character so they have to do an exact match? Or, are you simply trying to make a wildcard search like that work correctly/consistently?

We need to remove the wildcard option and have an exact match.

Hmm, it should work using the 0 value as far as I know. Perhaps the wildcard is still messing it up if they use it? Maybe you can try stripping it out using SUBSTITUTE(H35,”*”,””) instead of H35 in your formula. This will remove the asterisk by substituting it with a blank string before attempting a match. Best of luck!

Please help me to solve this issue

I have amount in G3 & H3, I want function in L3 ‘if there is no value in H3 no need function and the amount should come as zero’, if there is any amount in H3 then (H3<G3) then G3-H3

I have multiple columns to link and v lookup please help me

Bank Code Branch Code Branch Name

1 1 AAA

1 2 BBB

1 3 CCC

2 1 DDD

2 2 EEE

2 3 FFF

3 1 GGG

3 2 HHH

3 3 III

If Bank Code & Branch code given, I need the Branch name to be displayed

Ex Bank: 2

Branch :3

I need system to display FFF

You may want to use some hidden fields and make a composite field with a two-digit number, if that works. For example, add a column that combines column 1 and 2 (11, 12, 13, 21, 22, 23, etc.) Then, add a similar field for the Bank and Branch fields (e.g., 23). Then, do your INDEX-MATCH or VLOOKUP using that composite field instead of the two individual fields. I hope that works for you!

How can I pick multiple row data of all debit/credit entries of one cheque no?

Try taking at look at this related blog titled: How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

http://thinketg.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/

I ran across this as I was looking for a means of improving vlookup() performance. Although, I was fine with vlookup()’s constraints in the particular application you gave me a different way of looking at the solution space. This was an unexpected surprise. Thanks!

Hello Ben.

I’m having a hard time trying to figure out which formula to use to find out the order total in L7. I want to link the prices of each product on the right side(T8) to the column in L7. I have tried using vlookup and the index-match formula but it still gave me #N/A. Please consult. Thank you.

This is the sheet:

http://i59.tinypic.com/2uqyxqe.jpg

Hello Emma.

I believe you’ve got your ranges reversed. Try the following:

=INDEX($T$8:$T$22,MATCH(J7, $S$8:$S$22))

Hi All,

I’ve sat trying this lookup/index function with no avail.

I have a master list of staff names and columns are approx 12-off across with different info. For now, I want to extract, or lookup/index, from a master list (which happens to be on its own tab and sorted by last name).

columns eg,: name, company, office location, department, etc, etc,

okay, so i will have some repeats on company, office location, department. what i want to do is to create a tab for every seperate office/department and set it to lookup/index from the master tab and list all the, for example, staff per department. so i’ll create a tab called, and this the department names, design or manufacture. then set a filter in each, by means of changing one cell, to the diffirent departments.

i can get it to run, sort of, but it seems these functions dont like to be moved down. in other words, my names only start in row 4 due to the headings, etc.

fyi, there are 3 companies, 4 locations, 19 departments and approx 380 staff members. and for now all i want is to extract the “per department” staff names.

any help/ideas?

Hi,

Are you using absolute cell references ($A$1 and so on with the dollar sign)? That might get it done for you…

I had used both LOOKUP and INDEX/Match methods to get a value from the reference table shown below to try to return exact match value.However, the returned value of *10 is corresponding to the one of *1 (0.5 instead of 1).

Lookup_vector Result_vector

(Lookup Range) (Index Range)

*1 0.5

*2 3

*6 0

*10 1

Your help would be greatly appreciated!

Interesting. My guess is that the problem has to do with the asterisk (*) in your cell. That could be causing the formula to match as a wildcard. I suggest trimming that character off when you do your INDEX-MATCH or LOOKUP compare. For example, here’s how to trim off the first character from a value in cell A1:

=RIGHT(A1, LEN(A1)-1)

Hope that helps!

Hi…Stuck up with a problem..

In sheet 1, I have entry date of an invoice and user name.

In sheet 2, I have user name and start and end dates of his holidays.

I want to track if that user has entered an invoice when he was on holiday…

I guess I need to match both, the user name and if date is falling between start and end dates….

Can anyone help…

Cheers

Hi… That’s an interesting problem!

You can check if a date falls within a range using a formula like this:

=IF(AND([invoice date]>[start date], [invoice date]<[end date]), "Yes", "No") To make that look up by user name, replace [start date] and [end date] with INDEX-MATCH formulas that look up the vacation start and end dates by matching the user name. Place the completed formula into a "On Holiday?" column in Sheet1 and it should get the job done! Best of luck.