Extract values with non-array formula if condition met

Looking for non-array formula to extract values starting from A32 (Unique ID heading) and B32 if the two conditions names (from A4 - maximum names is 25) and year (three years and will be manually entered)). Two conditions to be met are names (A4-A28) and year (D1-F1) criteria. If year condition met, it should extract year values (from C32.....) into C4 based on the number of names supplied. It should also extract company name into company table (starting from N4....). Same for Unique ID. This should. The main pulling criteria is the name in A4-A28. See sample file.

Thanks

By non-array formula, do you mean the formula needs to work correctly without being entered as an array formula? IOW, formulas could be OK if they used arrays as intermediate values as long as array formula entry wasn't required?

Would the years in C4:L28 always be one of the years in D1:F1?

TBH, this would be much simpler were A31:D70 one table (Table1) and the data from A4:A28, C4:L48 and N4:W28 consolidated into a 4-field by up to 250 record table (Table2) and you used MS Query to join them and produce the unique IDs that way. The 4 fields would be Index (1 to 250 = 10 years/compaies x 25 people), Company (N4:W28), Year (C4:L28), and Name (A4:A28). Maybe also a Table3 with just one field, with 3 records for the year values in D1:F1. Use inner join queries of Table1 and Table2 separately against Table3 to pull records for only the years of interest. Call those FTable1 and FTable2. Then use an inner join of FTable1 and FTable2 on Name, Year, and Company, returning FTable1.[Unique ID] and FTable2.[Index]. The Index field can be divided into row [MOD(Index-1,25)+1] and column [INT((Index+24)/25)], and that can be used to fill out the 25-row x 10-column block of Unique IDs. Dunno whether MS Query supports crosstab queries.

Anyway, the following may be the simplest way to do this in Excel without array formulas.

A81: Index
B81: Company
C81: Year
D81: Name

A82: 1
A83: =A81+1

Fill A83 down into A84:A331.

B82: =T(INDEX($N$4:$W$28,MOD(A82-1,25)+1,INT((A82+24)/25)))
C82: =IFERROR(INDEX($D$1:$F$1,MATCH(INDEX($C$4:$L$28,MOD(A82-1,25)+1,INT((A82+24)/25)),$D$1:$F$1,0)),"")
D82: =T(INDEX($A$4:$A$28,MOD(A82-1,25)+1))

Select B82:D82 and fill down into B83:D331. Note that the col C formulas ensure than years from C4:L28 appear in D1:F1 or these formulas return "" which appear blank.

Now supplement with match-up formulas.

E82: =IF(COUNT(C82),MATCH(1,INDEX((D82=$D$32:$D$70)*(C82=$C$32:$C$70)*(B82=$B$32:$B$70),0),0),"")

Fill E82 down into E83:E331. This most definitely USES arrays, but it doesn't require array formula entry thanks to INDEX(array_expression,0).

Now populate N32:W56 with formulas.

N32: =IF(COUNT(INDEX($E$82:$E$331,ROWS($N$32:N32)+(COLUMNS($N$32:N32)-1)*25)),INDEX($A$32:$A$70,INDEX($E$82:$E$331,ROWS($N$32:N32)+(COLUMNS($N$32:N32)-1)*25)),"")

Copy N32 and paste into N32:W56.

Not particularly elegant. Doing this in MS Query (which comes bundled with Office) with SQL queries and ideally a CROSSTAB query at the end would be much more elegant. If there's a more elegant way to do this with non-array formulas, someone else will need to come up with it.

ADDED: A few minutes after posting this, the more direct solution hit me. Dispense with all the formulas above.

N32: =IFERROR(INDEX($A$32:$A$70,MATCH(1,INDEX(($A4=$D$32:$D$70)*(C4=$C$32:$C$70)*(N4=$B$32:$B$70),0),0)),"")

Copy N32, paste into N32:W56. I must have been way too fixated on the SQL approach. Note, however that this doesn't ensure that C4:L28 year values are in D1:F1. For that, use

N32: =IFERROR(INDEX($A$32:$A$70,MATCH(1,INDEX(($A4=$D$32:$D$70)*(INDEX($D$1:$F$1,MATCH(C4,$D$1:$F$1,0))=$C$32:$C$70)*(N4=$B$32:$B$70),0),0)),"")

hrlngrv: This formula pulled correctly for the Unique ID table (N32:W56) when the criteria in Table A4:A28 and B1:F1 is met. However, it is not pulling for C4:L28 when I copied the formuala into this table. It did not pull for N4:W28 as well. Are you able to modify this formula =IFERROR(INDEX($A$32:$A$70,MATCH(1,INDEX(($A4=$D$32:$D$70)*(INDEX($D$1:$F$1,MATCH(C4,$D$1:$F$1,0))=$C$32:$C$70)*(N4=$B$32:$B$70),0),0)),"") to pull for these two other tables?

Note: Would the years in C4:L28 always be one of the years in D1:F1? Answer: No, the years in C4:L28 are to be pulled by formula based on manual data entry in A4:A28 (names in A4:A28 are going to be manually entered) and the years in D1:F1 are to going to manually entered as well. I only used 2000, 2001 and 2002 for this sample. It could be 2001, 2002 and 2003 or 2002, 2003 and 2004. The goal is now for the formula to look through A32:D70 and extract the values for C4:L28 if A4:A28 and D1:F1 criteria is met.

Thanks