※ Download: Excel vlookup return multiple values
I still have blank cells between the data. If you want to get the list of all matching values, the VLOOKUP function cannot help, because it can return only one value at a time, alas. If you want to return multiple values vertically, you can read this article quickly convert multiple XLSX.
My data from this formula has multiple texts vertically and I would like each unique data only repeated once next to the first in the next column and blanks following until the next unique text. So, what are you supposed to do?
- Can you help me?
Fortunately, Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria. Unfortunately, Many cars, like the Acura ILX, have multiple engine configurations with different mileage ratings. This means, however, that we will need to look up the car by both its Model and its Displacement at the same time to find the appropriate Combined Fuel Economy in column H. Get the latest Excel tips and tricks by! Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at. Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts! It will certainly be useful for me in the future. Can you suggest how this approach might be modified or another approach entirely for use with two criteria, one of which is not exact? Specifically, I want to search an array where one value is an exact match, and the second value falls within a range specified by two columns — an upper and a lower bound. When both of these criteria are met, I want to output the value in a column adjacent to the values that were searched. Then I want to output the value contained in column D. Then you can copy formula into cells H3 through H13 EDITOR'S NOTE: Chuck, I corrected the formula you provided to the way you intended before approving it... Hello, In my scenario, I am using G2 as a date and would like to retrieve the date closest to the date provided on G2. Ideally I need to retrieve the exact match of the date on G2 but at times there will not be an exact match. If this occurs, I need to retrieve the closest date in the range whether it is before or after the date listed on G2. Is a formula for this feasible? Unfortunately, the equation in the previous post is a mess because it does not show the greater-than or less-than symbols, but instead removed the middle of the formula — the part that mattered the most. His formula checks to make sure Column B is less than the Column G item. Hi Robert, I am having a sheet with names in one column, and in another sheet with names and numbers. The difficulty i am facing is in sheet 2 same names are there for different numbers, so vlookup is giving the first match value and leaving the rest. I just learned the Index Match with multiple criteria following this great tutorial. Is there a way to get excel to look up the 1st, 2nd, 3rd etc. And thanks for any help you can provide Thanks, helped alot. Although I have a table of around 7000 and this method takes alot of time. What would be a faster way to do this. I think it is slow because it has to create an array for each cell. It does about 5. Just in case it is relevant, no other calculations are on the sheet, and I am using 3 critiera. Also, if you are working with the table often, it may be worth it to shut off automatic re-calculation and only re-run the 20 minute session when you are finished making changes. Andrew Thank you so very much for posting this. Again, thank you from all of us. Thanks for taking the time to write this fantastic explanation for using INDEX and MATCH for multiple criteria. Yours is the first article that explained the logic behind it. I never comment on blog posts, but I had to for this — it was excellent! Thanks for this great tutorial. I finally understand how to use the Index + Match function and was able to use it in a complex spreadsheet for work. In the end, I had to transpose the data from a vertically structured table to a horizontally structured one. Is there a way to use Index + Match for data that is structured from top to bottom? Either way, a great tutorial and well written blog. Keep up the good work! Thank you — this is a big help. I was able to do the formula with 3 variables. I am unable to just click the bottom right corner because I get a REF! The only way I can get it to work is hit the CTRL+SHIFT+ENTER in each cell, but I cannot do this 100K+ times. Any simple way to copy this formula down an entire column? Hi Katie, Congratulations on getting the formula up and running! Array formulas the ones you enter with CTRL+SHIFT+ENTER are a bit picky about how you copy them. You can select the cell with the entered formula and drag down with the bottom right corner to copy the formula down. You can also copy and paste the formula into other cells, but you must exclude the original cell from the paste selection or Excel will give an error. Be sure to save your work before copying the formula down and plan to do so when you are able to leave Excel alone to process for several hours. I have found it can be used in differnet circumstances, however I was wondering whether you can provide further explanation in regards to a particualr situation. I am trying to produce a report that checks for a name in table that is repeated multiple times and selects the the most recent entry each entry has a different date in a different column. I ma breaking my head trying to use your approach but not sure how it could make it work. Could you cast some light on this? Hi, I wondered if there was a way to do this within the formula using the MAX command somehow? Hi, I think there is an easy way to do it but you need a new column in your data sheet Please check below. You can even create as much criteria as you want. Regards The formula in C2 of the example worksheet is an array formula. These were available as early as Excel 2003. If there is a VALUE! Reply to: Unal comment below. Thank you very much. This formula has saved my life. This is genius thanks again!!!! Hi, I think there is an easy way to do it but you need a new column in your data sheet Please check below. You can even create as much criteria as you want. Regards I have a different and maybe easier challenge. I have several rows containing Jan 2015 and want to add up all values for Jan 2015. How do i do this? I want it to search through. D3 ,0 ,3 } How can I keep that fixed? I am using this to reference data from two separate Tables. I attempted to use the Table name and Table Columns as reference in the Match formula. I had to use absolute cell references — e. Any idea why this would be? Perhaps I am just referencing incorrectly? Cheers, Adam Hi Adam, Data Tables in Excel work on internal logic that lets you easily duplicate formulas all the way down the rows. Your reference Page is referring to a relative page in the row of the table. It is likely not interpreting this correctly for the MATCH, which generally works on absolute row references. Sounds like you got it to work, though! Andrew hi Andrew, thanks for this great tutorial. As a result, these formulas cannot be evaluated. Unfortunately this makes the combi un-usable. This is a great article but I have a slightly different issue. I have two sheets of data that have three of the same columns that I want to compare the row fields. If there is not a match from one sheet to another then I need to generate a report of those differences or at least hightlight the non-matches. Any ideas on how to do that? Any thoughts, or ideas? I would love to be able to do this without resorting to concatenating the two fields together to create the look-up value! I did a test on this and it worked perfectly, but all my data was on 1 sheet. The table is more a matrix so I have to have a formula for each row and column to find the value where they meet. Do you think a two criteria index match would have any performance boost in this instance? Thanks for your advice. This was a great guide. Can you please tell me if this is possible? I needed to create a table to look up commission rates where the rate could vary by day and by partner, but not for a uniform list. For example, for one partner, the rate may change on Oct, 1, where another one never changes and another one went up for a two month period and then changed to another rate. In my source data I added in an effective date for each change in rates which was the LAST effective date of the rates. The base effective rate was 10 years in the future. Worked very well, just the array is heavy on calculations. Happy to share if anyone wants any more info Avraham Thanks for the example. I have a question on just the MATCH formula. As per my understanding, MATCH formula only takes one look-up value, can I specify multiple look-up values where if one of the values is found, I would like it to return the result. Example: I have a row made up of a lot of empty cells, some cells marked as a, some as c, same as d, some as 4, some as 7. I want the formula to return the first relative position in a range where either of 4 or 7 are found while looking up. This saved me a LOT of time and effort messing around with VBA and writing a macro. Those two pieces of information are critical to making this work. Hi Andrew I loved the post thanks very much it solved one of the problems I had been banging my head on. Here is the issue. I need to return the largest value for each grouping DATA SET Expected Return blue 4 Blue 96 blue 16 blue 42 blue 96 Green 12 Green 18 Green 18 Orange 3 Orange 47 Orange 47 add 6000+ more lines Thanks in Advance for any help your able to offer Hi, thanks for the fantastic explanation. A couple of questions: — My formula is returning the value BELOW the one that I am expecting. What am I doing wrong? The criteria in my formula are definitely correct. Is this the most efficient way? Would it be better to use a sum if array formula? The report will be run on a weekly basis therefore the dates will change in each column. The problem is that the text names do not match exactly with a certain amount of variation. The only column that should match 1:1 is column C. However, the item may have more than one po. I need to have a list of the top five items in my list which I have created but I have two fields with the same number. I need to look for the header for that value but, the fields that have the same value the index matched the headers the same. All I want is to go to the next matching value and match the header. Currently this process has taken over a week and I am hoping someone can simplify this. I have a listing of employees who each have a current phone bill amount. EID Amount of Phone Bill I have a spreadsheet that provides me a listing of the Employees EID Job Name Job Hours % of Total Hours I need to merge these sheets together so I have 1 working sheet but my issue is an employee can charge several jobs and I can only get the 1st job to appear. Will excel know to add additional lines based on the number of jobs an employee charges? Any help would be greatly appreciated. Any help is much appreciated. Great tutorial, useful technique. Problem: I need a logical OR for the two criteria instead of an AND. Thus, if criteria1 OR criteria2 then value in return column. I would like to use multiple criteria but one should be 100% match and second should be just partial match. So for example Last name must match, post code must match but first name must match on first letter from check cell and first letter on check cells. Which means the formula is going to find the first set going down the list. The result is either Value! I am working in an xlsx file. Attached is a png of my source table. Many thanks for your help, Melinda This is great info. Can you help me resolve an error? A hearty Thank You! Building complex charts in Excel is a feat in itself, what with the dummy columns to put space betwen series to make them easier to visualize, so your explanation here lets me simplify at least the population of the cells in the chart that the table looks at. Alan I am trying to find an average of items sold on Monday, Tuesday, Wednesday, etc. I am trying to track sold items. There is daily tracking from Column J and on.. I hope my description is not too confusing. Appreciate the help in advance! I have my look up data which are numbers then I have a shelf number and then I have the number I have scanned in to the spread sheet. But it does show that the order of the lookup table is important, if this is not predictable then you may want to use a different method Thank you so much. However, I was hoping to set another filter to this array. I have a file with 600,000 rows of data. I was able to use your formula to pull back the data I needed based on 3 criteria points. It currently pulls back 30 — 40 rows from the 600K rows. Took me a while to understand and implement this DO NOT FORGET to hit Ctrl+Shift+Enter at the end of your formula PLEASE, AS INSTRUCTED HERE!!! I had two tables in excel that I had to join by Purchase Order Prefix, Purchase order Number, Page Nbr, and Line Nbr to link up the cost per unit with nbr of units and multiply the two together. MATCH … worked perfectly!!! I have one more question for you, though, as I have to do something more: I need also to sum the values found with this double comparison method. To better explain: I have values in one column, corresponding to countries and purchase orders. I need to sum and report in a different tab all what was invoiced per each country, based on the purchase order. With the INDEX + MATCH formula I manage to get only the first entry of the column where the double criteria PO + Country corresponds. Hi, thank you very much for the detail and clear instruction. It works perfectly if one of the criteria is not number though. What have I missed out? Hi I have a little question. But … I have another question. Thanks in advanced for your feedback! Your post nor the one above it does not give enough details to describe your situation. If you email me directly, I might be able to help. Feel free to send me your spreadsheet. You can make up fake data in it if you wish, or send me the actual data. Once I solve the problem for you, I will delete all traces of it on my system and never use it, publish it anywhere, or send it to anyone else. Hello, This was very useful, thank you very much. This works perfectly but the same as a normal VLOOKUP this brings the first viable answer. In my case I have three matches and would like to bring the last one. I could do this in a VLOOKUP by using the MAX function, is there a way I could incorporate that into this formula? Thanks for the tutorial, this worked great!! J2,0 ,3 ,0 Which is fine, but I have about 70 such spreadsheets each with 3 tabs and it's a pain to have different formulas depending on the 3 digit code. It was very useful for 2 criterias. But it only works when we press Ctrl + Shift + Enter. If someone is reviewing the sheet, who is not much expert in the excel, and he press F2 and ENTER. The whole formula will be gone in the relevant cell. Please let me know any Technique that the formula will be permanent even after pressing ENTER rather than Ctrl + Shift + Enter.
However, the function takes really long to load. We need to retrieve related values from another system based on these three columns. I thank you for reading and hope to see you on our blog next week. Here we have to find the Density of an Element whose atomic mass is 15 or slightly less. The same as in the example above, you will need to add a helper column to your lookup table with concatenated lookup values and this should be the left-most column of the lookup range. For example, A2 says Apple, B2 says 16, A3 says pear, B3 says 32, etc. Connect to Tables The first step is to get the table data loaded into the Query Editor. How to go about it. Below you will find a few examples of such formulas. The result will look something similar to this: How this formula works This is an example of intermediate to advanced uses of Excel that implies basic knowledge of and Excel functions. And this transforms the initial long and complex formula into a very simple one, like this: Tip.
