Forum Discussion
INDEX-MATCH Multiple Results Horizontally
You can try this formula (haven't tried it mysel) in cell F25 on CPP SPACE sheet:
You need to use an array formula that combines INDEX, MATCH, SMALL and IF functions to return multiple values horizontally based on a given criteria.
=INDEX(‘SHEET 2’!F$45:F$85,SMALL(IF(‘SHEET 2’!$C$45:$C$85=$D25,ROW(‘SHEET 2’!$C$45:$C$85)-MIN(ROW(‘SHEET 2’!$C$45:$C$85))+1,“”),COLUMN(A1)))
And then drag it across horizontally to fill the rest of the cells.
This should return the items that match the space code and are approved.
The basic syntax of the formula is:
=INDEX(return_range,SMALL(IF(criteria_range=criteria,ROW(criteria_range)-MIN(ROW(criteria_range))+1,“”),COLUMN(A1)))
You need to enter this formula as an array formula by pressing Ctrl+Shift+Enter.
I hope this helps.
You can try this formula (haven't tried it mysel) in cell F25 on CPP SPACE sheet:
You need to use an array formula that combines INDEX, MATCH, SMALL and IF functions to return multiple values horizontally based on a given criteria.
=INDEX(‘SHEET 2’!F$45:F$85,SMALL(IF(‘SHEET 2’!$C$45:$C$85=$D25,ROW(‘SHEET 2’!$C$45:$C$85)-MIN(ROW(‘SHEET 2’!$C$45:$C$85))+1,“”),COLUMN(A1)))
And then drag it across horizontally to fill the rest of the cells.
This should return the items that match the space code and are approved.
The basic syntax of the formula is:
=INDEX(return_range,SMALL(IF(criteria_range=criteria,ROW(criteria_range)-MIN(ROW(criteria_range))+1,“”),COLUMN(A1)))
You need to enter this formula as an array formula by pressing Ctrl+Shift+Enter.
I hope this helps.
- I am glad that this helped you.
I wish you continued success with Excel.Hello!
I have another problem – it’s the same but different and I’m hoping you might be able to help solve this too!
I’m able to get data to where I want it for the most part, EXCEPT that I cannot figure out (despite a billion various attempts) how to condense data from multiple rows into one.
I need to be able to input proposed features into table 1 by location and week. Only one offer would be entered per row but it could run for multiple consecutive weeks and they will likely not be entered in order by row. For example, row 5 shows “cake” in WK 5 – WK 6 but row 6 has “flowers” for WK 3 – WK 4.
Once an item is “approved” (column B), it would ultimately rollup into a new condensed table where all items for a particular location would be in one row and run horizontally by week. For example, in table 1, there are 3 rows for “location 1” but in the summary table, they would condense to only one row.
Table 2 shows the end goal.
I've tried using the previous formula, as shown in table 3, but only cells D20 and E20 populate correctly.
Table 1 is a working document that will be regularly adjusted so the summary table needs to auto update. I should also mention that I am using excel 365.
Fingers crossed that you know how to solve for this!
