Help with Aggregate and Match Function

Question

I have two tables of data in one sheet. In my second table I am trying to use the aggregate match function to (1) match text in my second table to my first table and, (2) based on the match, pull the MAX value (ignoring errors) among the data set in the corresponding row among 4 specific columns.

Below is the data. As some pieces do not have data (hence the errors) I want to ignore those pieces in my final outcome (therefore using aggregate).

Key points

Data to Populate: G21:G32 (i.e., [[MAX])

Matching D21:D32 (i.e., [[Occ Name]) to B4:B15 (i.e., Table20[[Occupations]])

Data set to reference: G4:J15 (i.e., Table20[[Current Average Wage]:[2-Year Earning Potential]])

Current formula: =AGGREGATE(4,6,INDEX(Table20[[Current Average Wage]:[2-Year Earning Potential]],,MATCH([@[Occ Name]],Table20[Occupations],0)))

Answer 1

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 2

How about

=IFERROR(AGGREGATE(4,6,INDEX(Table20[[Current Average Wage]:[2-Year Earning Potential]],,MATCH([@[Occ Name]],Table20[Occupations],0))), "")

Thank you for your reply! I attempted the new formula with IFERROR, however, it seems to be pulling the MAX for each column in the range when I'm hoping to get it for the 4 columns in the row that matches per the MATCH function. Below is what I got running the formula:

Answer 3

How about

=IFERROR(AGGREGATE(4,6,INDEX(Table20[[Current Average Wage]:[2-Year Earning Potential]],,MATCH([@[Occ Name]],Table20[Occupations],0))), "")