(VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,MATCH(D$9,Trans_CIQ!$A$8:$AZ$8,INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)),0)*100000)
This formula works for some reason but I don't understand how. I tried to follow it by separating the functions but still don't understand.
Vlookup(lookup value (company name), table array (where to look), col_index_num (which column to look in), [range lookup] (always 0)) gives value that matches conditions
Match(lookup_value (CapIQ chart title),lookup_array (where to look), [match type] (exact)) gives item number in a list
Index(array (the table), row_num, [column num]) returns the value in the cell
=(VLOOKUP($C10,Trans_CIQ!$A$8:$AZ$95,Trans_CIQ!$A$886,0)*100000)
INDEX(Trans_CIQ!$A$886:$AZ$886,1,0)
is actually return a 1 dimensional array of 52 values, to the match to search.0
in INDEX it returns the whole row or column @PeterH