The above formula uses AGGREGATE which performs array like operations without actually being an array, at least for some of the functions like 14 and 15. The 14 tells AGGREGATE to sort the results from largest to smallest, 15 would sort from smallest to largest. The 6 tells AGGREGATE to ignore results that result in errors.
This part gives AGGREGATE the row number that is currently being evaluated.
This part is the two conditions which both need to be true in order to for AGGREGATE not to receive an error. The * acts like an AND function. when both or either one is false, the results winds up being 0 which causes a divide by 0 error. If both results are true, then it results in 1 and the row number is not modified by dividing by one. You get a list of filtered results that match your criteria.
The 1 tells AGGREGATE to return the 1 results from the sorted list. This means that with multiple rows that match your criteria the last row that match is returned for AGGREGATE function 14 and the the first row is returned for function 15.
Now that you have the row number, that can be dropped into a full column reference for INDEX to pull the information from the corresponding row of the column.
Because AGGREGATE uses array like calculations, full column references within the AGGREGATE function should be avoid to reduce extra calculations which could in turn bog down your system. Within the AGGREGATE function, The range should be limited to your data.
Adjust the ranges to suite your data.
You did not state what you wanted as a result when something was not found in the list. Currently it will return the an error. To tidy that up you can wrap the whole thing in an IFERROR function and make it look like the following: