Forum Discussion
Solved
INDEX-MATCH Multiple Results Horizontally
I have no idea what I’m doing and really need help! I’m trying to create a formula that will automatically fill in ‘approved’ items from the bottom chart on the CPP SPACE sheet to the corresponding ‘space’ by week in the top chart. I have managed to get only the first match to populate in the top chart but need help to get all remaining matches to populate horizontally based on the allocated space.
The 2pk pillow is in space AA 56 – A and has been approved for WK 1 & WK 2. The futon is also approved for the same space AA 56 – A but for WK 3 & WK 4. I need help to get ‘futon’ to populate in cells H25 & I25 in the top chart. Since the shelf and tv stand have not been 'approved', they do not populate in the top chart so this is correct since I only want ‘approved’ items to populate here.
This is what the CPP SPACE sheet looks like:
The end result I am trying to achieve would look like this with the 2pk pillow and futon listed horizontally by the selected 'space' and 'wk':
The formula on the CPP Space tab in cell F25 is:
=IFERROR(INDEX('SHEET 2'!F$45:F$85,MATCH($D25,'SHEET 2'!$C$45:$C$85,0)),"")
I created an additional sheet called ‘SHEET 2” to get data to format correctly (I assume there is an easier way but I don’t know what it is!).
This is what SHEET 2 looks like:
The formula on SHEET 2 in cell A46 is:
=INDEX('CPP SPACE'!A46,MATCH("APPROVED",'CPP SPACE'!A46,0))
The formula in cell F46 is:
=IFERROR(IF($A$46="APPROVED",INDEX('CPP SPACE'!F46,0,1)),"")
Any suggestions on how to modify the formulas would be greatly appreciated!!
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.
