# Thread: INDEX MATCH formula HELP Thanks: 0 Likes: 0

1. ## INDEX MATCH formula HELP

 A B C D E F G H I 1 2 2662 50% a d 3 1331 20% b f 4 5 a b c d e f g h 6 2662 50% 0% 0% 50% 0% 0% 0% 0% 7 1331 0% 20% 0% 0% 0% 20% 0% 0% 8 9

Hi everyone

May I know what array formula should I type in Cell D2 in order to retrieve the result..

pls help, tried many INDEX MATCH formula but still failed

2. ## Re: INDEX MATCH formula HELP

Hi & welcome to MrExcel.
In D2 copied down & across
=IFERROR(INDEX(\$B\$5:\$I\$5,AGGREGATE(15,6,(COLUMN(\$B\$5:\$I\$5)-COLUMN(\$B\$5)+1)/((\$A\$6:\$A\$7=\$A2)*(\$B\$6:\$I\$7=\$B2)),COLUMNS(\$A\$1:A\$1))),"")

3. ## Re: INDEX MATCH formula HELP

Welcome to the forum

Another way with array forumula

 A B C D E F G H I 1 2 2662 50% a d 3 1331 20% b f 4 5 a b c d e f g h 6 2662 50% 0% 0% 50% 0% 0% 0% 0% 7 1331 0% 20% 0% 0% 0% 20% 0% 0%

 Cell Array Formula D2 {=IFERROR(INDEX(\$A\$5:\$I\$5,0,SMALL(IF(\$A\$6:\$A\$7=\$A2,IF(\$B\$6:\$I\$7=\$B2,COLUMN(\$B\$5:\$I\$5))),COLUMNS(\$D\$1:D1))),"")}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself