Lookup values in a range using two or more criteria and return multiple matches in excel
In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula to lookup values in range using one criterion and return (if possible) multiple adjacent values.
In this blog post I´ll show you how to lookup values in range using two or more criteria and return (if possible) multiple adjacent values using an array formula.
If you can rearrange your data, read this post:
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
You can use a lot easier array formula if you rearrange your data.
Array formula in B22:
copied down as far as needed.
Read an explanation of this array formula here:
Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
Named ranges
tbl (B3:G8)
search_tbl (B18:B19)
What is named ranges?
How to implement array formula to your workbook
Change the named ranges. Increase search_col range to add more lookup values. The bolded value is the specified (adjacent) column number you want extract from a matching value.
Download excel sample file for this tutorial.
Filter-cells-using-two or more-criteria-in-excel.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SMALL(array,k) returns the k-th smallest row number in this data set.
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference
COLUMN(reference)
returns the column number of a reference
Related posts:
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
Vlookup with 2 or more lookup criteria and return multiple matches in excel
Lookup a value in a list and return multiple matches in excel
Lookup two index columns using min max values and a date range as criteria
Recommended categories
Thanks for all the examples you've provided. I've been looking for a lookup like this one for some time and this does almost what I want. Unfortunately, I can't make enough sense of what happens internally to adapt it exactly - it feels like trying to visualise 4 dimensions in a 3 dimensional world. Ideally, I would like the results to appear in column-row order rather than the row-column order that appears here but can't work out how to do this let alone whether it is even possible. I also can't work out why various bits related to the column number in the index function are multiplied or divided by 16384. Can anyone point me in the right direction on this?
I'd also quite like to find a way of sorting the results by search term so that, for example, all the results for the first search term (here, Maine) are listed, followed by all the results for the second search term (here South Carolina), and so on. Again, is this possible?
I also found that changing the 1 prior to the /16384 had the same effect as changing the 1 after the *16384 but am not sure whether this was simply fortuitous or not.
Regards,
Ian
Ian,
Question 1:
I would like the results to appear in column-row order rather than the row-column order that appears here but can't work out how to do this let alone whether it is even possible.
Answer 1: The results are in a column in the above example. I guess you want them in a row?
Copy this formula into a cell:
=INDEX(tbl, SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), COLUMN(A1)), (SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), COLUMN(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), COLUMN(A1)))*16384+1) + CTRL + SHIFT + ENTER. Copy the cell and paste it to the right as far as needed.
Sorry, I wasn't clear, although this helps a lot with a couple of other things I'm looking at.
In the example you used, the formula effectively searches each row in the table in turn so that the first result is from the third pair of columns (2009 March), the second result from the first pair of columns (2009 January), the third result from the second pair of columns (2009 February). What this does is return all relevant results from the first row, then all relevant resutls from the second row, and so on.
What I'm trying to do is to return all relevant result from the first pair of columns, then all relevant results from the second pair of columns, then all relevant results from the third pair of columns, and so on (there could be 4 or 5 pairs of columns, possibly more), where for each pair of columns the results are returned reading down the columns. This would produce a set of results for your example which looks like:
Johnson
Miller
Taylor
Lopez
and so on and if South Dakota was added to the list of criteria, the results would look like:
Johnson
Williams
Miller
Wilson
Taylor
Lopez
I hope that makes more sense. But is it possible to do it like that? Thanks for your help so far.
Regards,
Ian
Ian,
Try this:
=INDEX(tbl, (SMALL(IF(COUNTIF(search_col, tbl)>0, (COLUMN(tbl)-MIN(COLUMN(tbl))+1)+(ROW(tbl)-MIN(ROW(tbl))+1)/65536), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, COLUMN(tbl)-MIN(COLUMN(tbl))+1), ROW(A1)))*65536, SMALL(IF(COUNTIF(search_col, tbl)>0, COLUMN(tbl)-MIN(COLUMN(tbl))+1), ROW(A1))+1) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed.
Thanks for commenting!
Thank you. That is exactly what I was looking for. And, even better, I think I'm beginning to understand how these things work, as well.
Thanks again.
Ian
Hello, not sure if this post is followed any more, but I am wondering if it is possible to complete the search as described above and concatenate all results into one cell? (I realize depending on the search, we could be looking at hitting cell text limits)
Thanks for any help you can provide!
Nevermind! I searched some of the other posts on this site and found that a UDF would do the trick.
THANKS!
Hello, this seems to be fairly similar to what I am trying to do but cannot seem to figure it out. I have 3 columns essentially and am trying to lookup a value from one column and return another column's entry. The problem is that my one column contains addresses and it does not match exactly because it may be missing an "Ave" or somthing of that nature. Here is what I am working with:
A1 A2 A3
5555 E Oakton St 65899 5555 E Oakton
726 S Oakley Blvd 55321 726 S Oakley
Here's the function =VLOOKUP(A3, A1:A2, 2, False)
It will not return A2 because A3 is missing Blvd, but if I use True, it will return a value from the previous row (65899).
I was thinking if there is any way of splitting up A3 into 3 different columns and using an AND statement in the vlookup to try and find just the first 3 entries (726, S, and Oakley) but that did not seem to work either. Any help would be greatly appreciated.