Returning a specific value based on one value in a range of cells.

Hello!

I am looking at two tables, and I need to return a "salary" (hourly wage) value based on an employee "step" that advances every year. In Table 2 below, in the orange fields, I'm looking to return the proper value, for each person, for the step they advance each year.

So, for example, in Status Quo Wages, in 2018 Abigail Brainard should be making $10.34, an Apple Picker's Step 3 Status Quo Salary.

I need to say, "Based on that year's Column F step in Table 2, xyz person will make the wages in Columns D through O." I've tried a bunch of nested IF functions but haven't quite got there.

I've attached a version of my table for reference.

Thanks!

Steven

is that what you want?

This is exactly right! Thanks - I'll try to port this formula to my real life example and make sure I understand it - will follow up with any questions.

Sandy666, this worked beautifully.

Only problem is, I can't really understand it intellectually/technically. Can you help me understand what is going on here?

I can't quite figure out why it turns it into essentially a VLOOKUP of distinct cells.

Oh it's literally just where the two criteria intersect in both ranges? Genius.

Do match criteria need to be located in a specific location, left to right? (For example if the contents of "Code" in Column C were actually in Column Z, would it still work?

INDEX works in both direction

Great. Thanks!

Glad to help

If problem is resolved read my "dancing" footnote

I'm going to play with this today and tomorrow and then will mark it resolved - thanks! Just in case I get something wrong.

so it will be next problem because this one from first post is resolved