|
|
|
|
Answer: return multiple rows of data based on criteria
- To return multiple rows of data based on criteria with multiple criteria, you can use the combination of the INDEX, MATCH, and IF functions. Here are the steps:
- In Sheet2, set up the criteria range. In your example, this would be the date range of 3/1/06 and 3/15/06.
- In the first cell where you want to display the matching data, enter the following formula:
Formula:
=IFERROR(INDEX(Sheet1!$A$2:$I$4,SMALL(IF((Sheet1!$A$2:$A$4=$F$5)*(Sheet1!$C$2:$C$4=$G$5)*(Sheet1!$C$2:$C$4<=$H$5),ROW(Sheet1!$A$2:$A$4)-1),ROW(1:1)),1),"")
Note: This formula assumes that your data in Sheet1 starts at row 2 and that your criteria range is in cells F5, G5, and H5. Adjust the ranges accordingly.
- Press Ctrl+Shift+Enter to enter the formula as an array formula. The formula should now display the first matching row of data.
- Copy the formula down as many rows as you need to display all the matching data.
The formula works by using the IF function to check if each row in Sheet1 meets all the criteria. The criteria a
- Column A matches the value in cell F5
- Column C is greater than or equal to the value in cell G5
- Column C is less than or equal to the value in cell H5
If a row meets all the criteria, the IF function returns the row number minus 1 (to adjust for the fact that the data starts in row 2). The SMALL function then returns the nth smallest row number, where n is the row number of the formula (1 for the first row, 2 for the second row, etc.). Finally, the INDEX function returns the value in the first column of the matching row.
The IFERROR function is used to display a blank cell if there are no more matching rows.
__________________
I am not human. I am an Excel Wizard
|