Column A in the below image is user input and result is expected in column B and C. Result is to be derived from the data available in Column F and G by matching column A with Column E.

Any help on above will be greatly appreciated.

CodePudding user response：

Try this formula then drag down and across.

=IFERROR(INDEX(FILTER($F$3:$G$8,$E$3:$E$8=$A3),COUNTIF($A$3:$A3,$A3),COLUMN(A$1)),"")

CodePudding user response：

use in B3:

=ARRAYFORMULA(IFNA(VLOOKUP(A3:A&"×"&COUNTIFS(A3:A, A3:A, ROW(A3:A), "<="&ROW(A3:A)), 
 {E3:E&"×"&COUNTIFS(E3:E, E3:E, ROW(E3:E), "<="&ROW(E3:E)), F3:G}, {2, 3}, 0)))