Index/match or vlookup a range where value matches in a table

I have been trying to figure out how to utilize a simple index/match, or even vlookup to return the dollar amount from the correct tier that also and matches the same Team ID.


I have a table that has different team IDs is column A, and 3 different point values in column D. depending on the points, is the dollar amount. I want it to look up the dollar amount for the point values of the team ID that matches item.


I want to return the dollar amount listed in Levels[Agent] based on how many points were obtained (G) will fall into 1 of 3 tiers in Levels[Total Points Range]


=INDEX(Levels[Agent],MATCH(G2,Levels[Total Points Range]))


=VLOOKUP(G2,Levels[Total Points Range]:Levels[Agent],2,TRUE)


BUT ALSO the Levels[Agent] amount that matches the Team ID (Column A) with the Team ID in Levels[Team]


below is the closest i have got, but it doesn't work as row 8 should not be returning any value as there is no amount listed in the Levels[Agent] column for that team


=INDEX(Levels[Agent],MATCH(A2&G2,Levels[Team]&Levels[Total Points Range]))

My attempts are in columns s, t, and v.




Please help!!!

I can’t open your workbook, but your match statements seem to be missing the final argument, which is 0 for an exact match and 1, which is what I think you need, for an approximate match.

=INDEX(Levels[Agent],MATCH(G2,Levels[Total Points Range]),1)

This is the same as the TRUE argument in your VLOOKUP.

thank you!

in the example you sent, including a 1 isn't necessary to get the same result. i was wanting to combine an approximate match with an exact match and it was not working.

If you leave the argument out, it defaults to a less than or equal to approximate match, so you are right, however I don't understand what you are trying to do, sorry.

Didn't have any trouble opening the workbook, and was able to come up with an array formula that appears to do what you want. Interestingly, your formula in column v was giving almost the same result. And with this simple modification I was able to get your formula to agree with mine


HOWEVER, you have to sort the Levels table so that the Teams are in alphabetical order (SUP01, SUP02, ...) and the Total Points Range is in numerical order (0, 10, 30). Your use of formulas within the table prevented it from sorting correctly so I eliminated them with a copy -> paste values.

Thank you so much!!! One of the many reasons i love excel. so many ways to get the same result. I couldn't figure out why the column V was almost right, but not quite. About drove me nuts!