Match/Index multiple criteria and return multiple criteria on different rows
I am looking for a formula that will allow me to lookup multiple criteria and pull in multiple matches. For example: My column headers are years, i.e. 2007, 2008, etc. My row headers are the different project types, i.e. New, Old, In Queue, etc. I also have 2 static combo boxes that correspond to City and State, which should also be part of the criteria and as the different city and state values from the drop-down change, the matches should changes as well. I know how to use MATCH and INDEX for multiple criteria to bring back a single match, but what if I need to match multiple criteria to pull in multiple matches for that same criteria and pull the matches in on separate rows if there is more than one match? I understand that I can use SMALL, but I've been playing around and have not been successful. I would appreciate any assistance with the array formulas that you can provide. Thanks!
Subodh Tiwari (Neeraj)
Uploading a small sample workbook with mocked up desired output manually on another sheet would be helpful.
Not sure I understood but looks like a job for an array formula.
ASKER
I've attached a file which should be helpful. Please note that I expect to populate the matrix based on the multiple criteria in the Sample Data tab. Additionally, the City and State on the Matrix Tab is a toggle field and won't match the Sample Data tab necessarily. But whatever is selected in those 2 fields in the drop- down boxes, should also be part of the multiple criteria in the formula. You'll see that under each action several values can be returned based on the date and the City and State, as I just noted.
Sample-Workbook-Matching-Multiple-C.xlsx
Sample-Workbook-Matching-Multiple-C.xlsx
ASKER
Perfect, thanks Scott! Have a great weekend!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you have Excel 2010 or later, you have the AGGREGATE function. It can do the same job as the SMALL function, with the advantage that the resulting formula does not need to be array-entered.
The layout in the posted workbook makes things more difficult for the formula because the job status is not repeated on each row, and because the city and state are returned using selections from a Forms combobox. Data...Validation...List dropdowns instead of those Forms comboboxes would have allowed me to eliminate the second and third INDEX function in the final formula.
Nevertheless, with the specified layout you can use a formula like:
The layout in the posted workbook makes things more difficult for the formula because the job status is not repeated on each row, and because the city and state are returned using selections from a Forms combobox. Data...Validation...List dropdowns instead of those Forms comboboxes would have allowed me to eliminate the second and third INDEX function in the final formula.
Nevertheless, with the specified layout you can use a formula like:
=IFERROR(INDEX('Sample Data'!$E$2:$E$26,AGGREGATE(15,6,ROW('Sample Data'!$E$2:$E$26)/(('Sample Data'!$A$2:$A$26=$A$4)*('Sample Data'!$B$2:$B$26=INDEX($H$1:$H$4,$A$1))*('Sample Data'!$C$2:$C$26=INDEX($I$1:$I$4,$B$1))*('Sample Data'!$D$2:$D$26=B$2)),ROWS(B$4:B4))-ROW('Sample Data'!$E$2)+1),"")ASKER
Thank you ProfessorJimJam for your diligence to this question. It was quite challenging, but I sincerely appreciate that you persisted until you found the right solution. Cheers!
Thank you byundt, I was able to utilize the previous solution, but it's great to know I have an alternative as well! I appreciate you taking the time to work on a solution!
Thank you byundt, I was able to utilize the previous solution, but it's great to know I have an alternative as well! I appreciate you taking the time to work on a solution!
You are welcome. Thanks for your feedback.