REVERSE VLOOKUP or DYNAMIC INDEX MATCH

Trying to figure out how to look for a country name given the number in a range of data. Have attached a file to be more clear.

Not sure if a combination of index match can do the trick, have been unable to figure it out.

The highlighted cell in yellow is the output I am trying to get.

You can use this array* formula in cell N11:

=INDEX(INDEX($H$5:$I$9,0,MATCH($N$5,$H$4:$I$4,0)),MATCH(1,($E$5:$E$9=$N$6)*($G$5:$G$9=$N$8),0))

and this array* formula in cell N12:

=INDEX($F$5:$F$9,MATCH(1,($E$5:$E$9=$N$6)*($G$5:$G$9=$N$8),0))

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Hope this helps.

Pete

When I change the rank (to the second largest), it should say Germany. I want it to be dynamic so that it matches the number of the output if possible.

So, in this case: Given the number is 4 and the destination is US, give me the origin.

Does that make sense? Thanks

This is almost perfect! It does not work when i change the code from 1 to 2.

Let's say I have year 2018, code 2, rank 1, destination US.

If i have those, the origin should say germany, but it says US.

Is there a way to change it?

There is a way I figure how to do it, but it is not dynamic for years.

Is there a way to change this formula to change it so it is dynamic?

=INDEX(F5:I9,MATCH(N11,H5:H9,FALSE),1)