need help using index/match to search grid

**tsiguy96** · 12-11-2013, 08:21 AM

attached is the sheet i am trying to make function...
this sheet, the yellow bar is a dropdown of the the names of the 5 bars to the left, i want the white boxes under it to return the values of the information UNDER the bars on the left. for example, if A is selected, all the white boxes would fill with 1, if B is selected, it would return all 2's since that is whats to the left.

i believe index match is the best way to do this, unless i am mistaken for a better way to search a grid? thanks all.

demo.xlsx

**benishiryo** · 12-11-2013, 08:30 AM

hi tsiguy. try this in J11:
=INDEX($A$1:$H$35,MATCH($J$9,$A$1:$A$29,0)+1+ROWS(J$11:J11),COLUMNS($J11:J11))

**ramananhrm** · 12-11-2013, 08:33 AM

Please try this function across J11:Q15

=INDEX(($A$3:$H$7,$A$10:$H$14,$A$17:$H$21,$A$24:$H$28,$A$31:$H$35),ROWS(J$11:J11),COLUMNS($J11:J11),MATCH($J$9,{"A";"B";"C";"D";"E"},0))