Using a multiple arrays in order to find the lowest "Ranking" in an character defined ranking system

Question

Wordy title but I was interviewed on this question, couldn't derive the answer and really would love to better understand array usage in excel.

Question: You have two arrays. One depicts the credit ratings given by various companies (i.e. Company 1, Company 2...) and then an array showing the rankings of all credit ratings. Your goal is to use a formula to find the Lowest Rating by "CUSIP" and which company gave this rating. NOTE: A rating of NR = "NOT RATED" and should be excluded.

Arrays (Red & Black Array is the desired outcome of the formula)

Answer 1

Figured out my own answer... Thought I would document it here for you. One of the issues I had in the interview was to do with me setting up the "Ranking" table as a vertical table rather than a longwise table. This did not allow the max-matching array function to properly work.

1. I had to rearrange the ranking table to be elongated: | AAA | AA | A |
2. Lowest Rating Formula: =INDEX((Ranking Table Array), 2, MAX(IF(Credit Ratings Array)<>"NR",MATCH((CUSIP row in the table), (Credit Ratings Array),0)))) I found this to accurately match the credit ratings with the lowest rated (would rank higher than a higher rated bond) for all of the companies
3. The company formula: INDEX(Table1[[#Headers],[Company 1]:[Company 3]],1,MATCH(C11,B4:D4,0)) Piggie backing off the other formula - this formula matches the lowest credit rating found in the last formula to the company rating that in each row.

Issues with answers:

• They require the answer table to be in the same format as the data being read. Because the formulas assume that CUSIPs ascending are being assessed the formulas will move 1 row down with each shift of the formula down. This is not scalable (even though this is an interview question).