Index formula excel

Question

my formula below is giving me results from correct columns but from WRONG rows. On sheet Dashborad I am inputing part number into P4 which will be searched in sheet Tooling in column A and then first value from sheet TOOLING, from column H will be returned:

=INDEX(TOOLING!$H:$H,SMALL(IF(DASHBOARD!$P$4=TOOLING!$A:$A,ROW(TOOLING!$A:$A)-ROW(INDEX(A$1,1,1))+1),1))

(Then I will use it for second and third found values)

Thanks for whatever advice.

Answer 1

If tooling!a:a contains text then,

=INDEX(tooling!H:H, AGGREGATE(15, 7, ROW(tooling!A$1:INDEX(tooling!A:A, MATCH("zzz", tooling!A:A)))/(tooling!A$1:INDEX(tooling!A:A, MATCH("zzz", tooling!A:A))=DASHBOARD!$P$4), ROW(1:1)))

If tooling!a:a contains numbers then,

=INDEX(tooling!H:H, AGGREGATE(15, 7, ROW(tooling!A$1:INDEX(tooling!A:A, MATCH(1e99, tooling!A:A)))/(tooling!A$1:INDEX(tooling!A:A, MATCH(1e99, tooling!A:A))=DASHBOARD!$P$4), ROW(1:1)))