There are often times in Excel you want to look up a value in a table based on another cell. Using VLOOKUP or HLOOKUP is a popular method for doing this quickly and easily. (Or, perhaps you read about my improved INDEX-MATCH solution in a previous blog post.) Either way, these solutions are limited to only returning one match. If there are multiple matches, it simply returns the first match only. So what if you need to see multiple matches? You need to be introduced to using Array Formulas in Excel–or as I like to call it: The Dark Side Of Excel.
Array Formulas in Excel are like normal formulas, but they are designed to work off a series of cells rather than single cells. With Array Formulas you can perform normal Excel operations on specified cell ranges. For example, you can use it to quickly create totals and subtotals on columns.
Say you have a spreadsheet of animals and their corresponding name. You want to be able to input “Dog” and get a list of all the dogs on your spreadsheet. Here are steps to use an Array Formula to return multiple values that match a lookup value in a list:
- Fill Column A with the lookup reference you want to match against. In this example, we’ll use type of Animal.
- Fill Column B with values you want the formula to return. If this example, we’ll use Name.
- Enter this formula:
1=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)) - Make sure you press CONTROL-SHIFT-ENTER in the cell to convert it to an Array Formula!
- Copy the formula down to allow it to list the subsequent matches, if any.
How does it work? Well, the SMALL() section of the Array Formula identifies the row numbers that match your selection. It uses the row number to return the value in that row only. When you copy the formula down, it automatically jumps to the next matching row number and returns that value, until it can’t find anything and errors out (resulting in a blank cell).
If you want to play with this example, download the sample spreadsheet. Some tips:
- Every time you modify an Array Formula, you must press CONTROL-SHIFT-ENTER so Excel knows it is an Array Formula
- If you insert a row or column, it will probably break your Array Formula and you will have to recreate it
- Array Formulas take a lot of processing power, so be careful using them on large sets of data
Array Formulas are very powerful once you wrap your head around how to use them. Enjoy!
Hi Ben,
Thanks for this article, it was very helpful for me as an Excel beginner.
I have one question: is there a way to adapt this formula so that it picks up partial matches ie wildcards?
Thanks again!
Try replacing the references above for $A$1:$A$8=$E$1 with:
FIND($E$1,$A$1:$A$8)
I haven’t tested this, so I’m not sure if FIND() is compatible with Array Formulas. If that doesn’t work, you may want to try SEARCH() instead like this:
NOT(ISERR(SEARCH($E$1,$A$1:$A$8)))
Best of luck! Hope one of those works for you.
Hello – did you have success with either the FIND or the SEARCH function? I was not able to get either of these to work.
Thanks for any additional insights!
Too bad that didn’t work! I guess those functions don’t work with Array Formulas. Otherwise, I can’t think of a good way to adapt this for wildcard matching. Sorry!
Dear Ben,
Very helpful! Thank you.
Does this also work when you use a data sheet (A1:B8) and a lookup sheet (D1:E7)?
Thanks,
Gijs
Solved it! I also needed to change Row(1:1) to Row(‘Data’!1:1)
Is there a way for a certain match to be returned such as the second or last match?
I can’t think of a way to do that directly in the formula… Indirectly, though, you can generate the full column of results using the array formula, then use the following formula to get the last item in that column. Assuming your results are listed in column B:
=INDEX(B:B,COUNT(B:B))
No the greatest solution, but maybe that will work for you!
I found this article very helpful.
Question: Is there a way to adapt this formula to vba to automate this process when pressing a button?
Great question! Luckily, you can apply Array Formulas to a range of cells using VBA. Here’s how:
Set targetRange = [range]
targetRange.FormulaArray = “=[array formula]”
That should apply the formula in an automated fashion for you. Best of luck!
How do i use this to get results from a different sheet?
Put SheetName! in front of those cell references. For example, A1:A5 would become Sheet1!A1:A5 to reference those cells on a sheet named Sheet1.
Hi,
This article is very helpful – but I have a specific situation where I actually want an error message (or a flag/conditional format) if there are multiple values possible. I am using excel to manage a music database – song title, artist, album etc.
On a separate spreadsheet (same workbook) I am compiling playlists – and started using vlookup to auto-fill artist and catalog information when I enter the track name.
If there is more than one song with the same title, I don’t need the alternatives listed – just a message that I need to look it up.
Is there a way to do this (relatively) simply?
Thanks,
LC
Hi,
Hopefully, if I’m understanding right this is relatively simple! Add a COUNTIF() function using a wildcard to determine how many potential matches there are. Then wrap that in an IF() that sets a message if it’s greater than 2, or runs the INDEX-MATCH if it’s 1.
Best of luck!
How do I return the values in a single cell separated by a comma? Thanks!
Try this: Follow the instructions above, and then combine the row data using a second formula that concatenates the results into a list. Finally, hide the column with the row-based results (or put in a different sheet altogether). Hope that works!
Hello,
Thank you so much for your example, I applied it to sort ingredients depending their weight in a recipe. My problem is that I may have 3 ingredients that weight 1000 and then 3 ingredients that weight 500. In this case the formula work until the 2nd match for 500g. I noticed that I should start over the row data when I reach another weight. How could I tell excel that when it reaches a new weight then the formula should restart the raw to (1:1) ? I don’t know if you understood my questions! :) =INDEX($G$8:$H$111;SMALL(IF($G$8:$G$112=L21;ROW($G$8:$G$112));ROW(1:1))-7;2)
Hello,
I guess I’m not understanding how you’re using the formula to sort… Perhaps this solution is overly complex to what you’re doing? There might be a better way to sort and filter ingredients that doesn’t use an array formula. Or perhaps I’m not understanding at correctly at all! Sorry I’m not sure what to tell you.