Lookup with multiple criteria and display multiple search results using excel formula, part 4
Question: I second G's question: can this be done for more than 3?
i.e.
(Instead of last name, middle, first)
customer#, cust name, appt date, appt time, venue, coordinator, assistant
A question asked in this post:
Lookup with multiple criteria and display multiple search results using excel formula, part 3
Answer:
Array formula in B25:
Formula in C25:
Formula in D25:
Formula in E25:
Formula in F25:
Formula in G25:
Formula in H25:
Formula in I25:
Named ranges
Customer (B3:B12)
Cust_name (C3:C12)
Appt_date (D3:D12)
Appt_time (E3:E12)
Venue (F3:F12)
Coordinator (G3:G12)
Assistant (H3:H12)
Search_customer (D15)
Search_cust_name (D16)
Search_Appt_date (D17)
Search_Appt_time (D18)
Search_venue (D19)
Search_Assistant (D20)
Search_Coordinator (D21)
What is named ranges?
Download excel example file.
Lookup with multiple criteria and display multiple search results.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
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
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
ROW(reference) returns the rownumber of a reference
COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers
SMALL(array,k) returns the k-th smallest row number in this data set.
ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE
SUM(number1,[number2],)
Adds all the numbers in a range of cells
SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)
This blog article is one out of five articles on the same subject.
- Search for a cell value in an excel table
- Search for a text string in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
- Lookup with multiple criteria and display multiple search results using excel formula, part 4
Read more related articles in the archive.
Related posts:
Lookup with multiple criteria and display multiple search results using excel formula, part 2
Lookup with multiple criteria and display multiple search results using excel formula, part 3
Lookup with multiple criteria and display multiple search results using excel formula
Lookup with multiple criteria and display multiple unique search results (array formula)
Search and display all cells that contain all search strings in excel
Recommended categories
Hi Oscar, love your blogs! great tutorials. Keep them coming!
Chrisham,thanks!
Thank you for the example. However I stillhave a problem that do not fit on your examples.
I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different dates. If I work with vlookup or Index-match I got only the first price for a certain securities. So I am not able to find the price of a securities that match both the name of the securities and the date. Could you advice if there is any way to overcome?
Paolo,
See this blog post: http://www.get-digital-help.com/2010/02/11/match-two-criteria-and-return-multiple-rows-in-excel/
Hi Oscar,
I've got a problem using your suggested approach. I have a list which I need to search from. I have three search criteria, and wish to output a fourth item. For each combination of the search criteria, there will only be one matching result.
I'm trying to use a series of search criteria (arranged in a table - with one row for each search instance, one column for each criterion) which is located on a different worksheet within the same workbook to the data table from which I need to extract the values.
The first search returns the desired result, however the next row returns #NUM error. In order to replicate the formula on the second and subsequent rows I've used a straight forward copy/past - the formula appears to be an Array one still (preceded and suceeded by {} as appropriate).
Do you have any suggestions which might help?
Do you have any suggestions on what might be causing this?
Oscar,
You are doing exacting what I have been trying to do for my Excel file but I cannot seem to get mine to work for some reason. Do you think you could take a look at my file if you have a chance. I would greatly appreciate it.
Bryant,
http://www.get-digital-help.com/contact/
Hi, Can you do this with a greater than date?
Arielle,
Array formula in cell B25:
=SMALL(IF((COUNTIF(Search_customer, Customer)+COUNTIF(Search_cust_name, Cust_name)+COUNTIF(Search_Appt_time, Appt_time)+COUNTIF(Search_venue, Venue)+COUNTIF(Search_Coordinator, Coordinator)+COUNTIF(Search_Assistant, Assistant))*COUNTIF(Search_Appt_date, "<"&Appt_date), ROW(Customer)-MIN(ROW(Customer))+1), ROWS(B24:$B$24)) + CTRL + SHIFT + ENTER.
Copy cell b25 and paste it to the cells below, as far as needed.
Hi,
Can you search within the whole excel workbook instead of just the sheet?
thanks
Also, what if I want to search 2 or more words within a column but they are not together (ex. 1 of the cell stated "hamburger, hotdog, soda", can i search for both hamburger and hotdog if are not side by side?)
thanks for your help
Oscar, great tutorial but i have one question.
In your spreadsheet at the top of the article your search results show a 3rd line that show #NUM1 for each answer.
Is there a way to show this as a blank cell instead?
cheers
Mark,
Yes!
=IFERROR(array_formula, "")
Don´t forget to enter it as an array formula. It works only in excel 2007 and 2010.
sorry #NUM1 should be #NUM!
cheers
Hi Oscar,
I'm trying to implement something similar, but using the customer name and between 2 different date ranges, so will need to use >= Appt date1 and <= Appt date2. I've been trying and trying but keep getting the good old #NUM! error!!!
Please help!
Thank you!!!
John
John,
See attached file:
Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john.xlsx
Hi Oscar,
I really appreciate you getting back to me. Thanks for the file but testing the formula is not producing the results I expect. Let's just simplify the formula and use only the following 3 criteria:
=IFERROR(SMALL(IF(ISNUMBER(SEARCH(Search_cust_name,Cust_name)*COUNTIFS(Appt_date,">="&Search_Appt_date1)*COUNTIFS(Appt_date,"= Search_Appt_date1 AND <= Search_Appt_date2. The string in Search_cust_name MUST match exact. The way the formula is right now still generates results if part of the Search_cust_name parameter matches and even worst if all the criteria values are blanks.
I hope I am explaining myself correctly. Your assistance will be forever appreciated!!!
Thank you,
John
Sorry, that didn't post correctly. I'm going to try again...
Please ignore previous post as it got truncated. Hopefully this will post properly:
Hi Oscar,
I really appreciate you getting back to me. Thanks for the file but testing the formula is not producing the results I expect. Let's just simplify the formula and use only the following 3 criteria:
=IFERROR(SMALL(IF(ISNUMBER(SEARCH(Search_cust_name,Cust_name)*COUNTIFS(Appt_date,">="&Search_Appt_date1)*COUNTIFS(Appt_date,"= Search_Appt_date1 AND <= Search_Appt_date2. The string in Search_cust_name MUST match exact. The way the formula is right now still generates results if part of the Search_cust_name parameter matches and even worst if all the criteria values are blanks.
I hope I am explaining myself correctly. Your assistance will be forever appreciated!!!
Thank you,
John
Hi Oscar,
Just wanted to let you know that my issue has been resolved so please disregard. I was able to incorporate your idea and nested a few other formulas to control the accuracy of the matching results that I expect. Once again thank you for your time as it is very much appreciated!
John
John,
I am going to answer your question anyway. The following formula filters records that exactly match (case insensitive) a criterion or criteria. An empty criteria is not calculated.
Array formula in cell B25:
See attached file:
Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john.xls
Sorry Oscar but I must be providing a special tag in my post that is messing it all up and it doesn't make sense.
To clarify, let's just simplify the formula so it only checks for an exact match of the Search_cust_name AND between Search_Appt_date1 AND Search_Appt_date2. The way the formula is right now still generates results if part of the Search_cust_name parameter matches and even worst if all the criteria values are blanks.
Please advise and my apologies for the confusion with all the messed up posts!
John
Hi Oscar,
Thank you very much for that solution. it solved my problem !
Just one more question, and I am not sure if the above solution will work here. The problem is with regards to analysing the remaining life of my inventory based on the quantity available and the monthly consumption. Its as follows :
Assuming today's date is 1st of September. I have 20 packets of cheese and the average monthly consumption is 4 packets. Ideally if the shelf life of those 20 packets is 6 months or more, then I can safely say that the total quantity will last for 5 months (20 divided by 4).However, with a slight twist in the data, assume that out of those 20 packets, 6 packets expire on 15 Oct, 7 packets expire on 10 Nov, and the remaining 6 on 15 December. Now I know that the total inventory is not gonna last for 5 months due to various expiry dates. If I calculate this manually, I come to know that the quantity will last for 3 and a half months.
How can i put all this in a formula to get the right answer ?
Please advise.
Thanks
Haroun
Haroun,
See this post:
Inventory consumption
[...] in Count values, Dates, Excel on Oct.05, 2012. Email This article to a Friend Haroun asks:The problem is with regards to analysing the remaining life of my inventory based on the quantity [...]
[...] Search Formula Hi, I wonder whether someone may be able to help me please. From this site Lookup with multiple criteria and display multiple search results using excel formula, part 4 | Get ... I've put together this formula, which works as expected: [...]