Index/Match with multiple results help needed

[c1nn4m0n]

Hello! I am attempting to populate a series of cells (sheet 1, C4-I4) with store numbers based on users inputting a market name into sheet 1 B2 and using an index/match to return up to 7 unique matches in C4-I4. I have been able to write a formula to populate the cells with matches, but when I have less than 7 matches results are input in my cells that are not a match when I want my cells to remain blank if there are no more matches.

Here is the formula I am using: =INDEX('Quarterly Results'!$A:$A, MATCH($B$2, 'Quarterly Results'!$B:$B,0)...adding +1 to +6 in subsequent cells.

Any help would be SO greatly appreciated!!

 

[Marcelo Branco]

Welcome to Mr Excel

You might wrap your formula with IFERROR and automate the +0, +1..+6 (if the values in Column A are grouped)

Something like this
C4
=IFERROR(INDEX('Quarterly Results'!$A:$A, MATCH($B$2, 'Quarterly Results'!$B:$B,0)+COLUMNS($C4:C4)-1)),"")
copy across till I4

Hope this helps

M.

 

[c1nn4m0n]

Thanks for the reply! I actually get an error when I copy over the formula you provided "Your formula is missing a parenthesis--) or (. Check the formula, and then add the parenthesis in the appropriate place."

I'm fine manually entering the +1, etc as the formula will be static in these 7 cells only, I just need to figure out how to wrap my INDEX/MATCH with an IFERROR that will return a blank when there are less than 7 matches in "Quarterly Results'!$B:$B (SO when there are only 3 matches, 4 cells will result in blanks - right now they are pulling in other store numbers that are not resulting in a match in QR B:B)

 

[Marcelo Branco]

Sorry, i misunderstood your question

Try this formula in C4 copied across
=IF(COUNTIF('Quarterly Results'!$B:$B,$B$2)>=COLUMNS($C4:C4),INDEX('Quarterly Results'!$A:$A,MATCH($B$2,'Quarterly Results'!$B:$B,0)+COLUMNS($C4:C4)-1),"")

M.

 

[c1nn4m0n]

That did it! THank you SO much!!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.