Index-Match drag in rows Horizontally

Hi all,

Please see attached file and help me to drag Index-Match formula in rows instead of columns.

Thanks in advance

Originally Posted by GC Excel

Hi,

Try this formula in J1 and drag to right:

Please Login or Register  to view this content.

Thanks for your quick response.
this formula does not work, it return only one value A when drag.

I would suggest that you put this formula in C1:

=IF(A1="","-",A1&"_"&COUNTIF(A$1:A1,A1))

and copy this down to below your data (the hyphens will indicate where it is active). Then you can put this formula in J1:

=IFERROR(INDEX($B$1:$B$16,MATCH($I1&"_"&COLUMNS($J:J),$C$1:$C$16,0)),"")

which can be copied across and down as required.

The attached file demonstrates this.

Hope this helps.

Pete

ARRAY formula in J1, then dragged across

Please Login or Register  to view this content.

Thanks guys.
you are all amazing.