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

#### cderhay

##### New Member
I have been trying to figure out how to utilize a simple index/match, or even vlookup to return the value that is in that column 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 (G2) 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]))

#### Attachments

• screen 1.PNG
34.7 KB · Views: 5
• screen 2.PNG
57.2 KB · Views: 5

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### jasonb75

##### Well-known Member
You're using approximate match, not exact match. This means that when there is no exact match the closest match (less than the exact match) is returned instead.

To only get exact matches, you need to use

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

This will show an error when there is no match, so you would need to use an error trap to keep your sheet tidy.

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

#### cderhay

##### New Member
thank you!!!

Unfortunately that is part of my dilemma. The approximate match was due to the fact that the points will fall in 1 of 3 tiers, and will not match exactly. That's the trouble i seem to have. how do i match exact the team, then look for the tier?

#### jasonb75

##### Well-known Member
Now I see the problem, as the result is a numeric (cash) value, I would suggest using a totally different method.

=MAXIFS(Levels[Agent],Levels[Team],A2,Levels[Total Points Range],"<="&G2)

edit:-

If that doesn't do what you need, then you could try

=INDEX(Levels[Agent],MATCH(A2&LOOKUP(G2,{0,10,30}),Levels[Team]&Levels[Total Points Range],0))

#### cderhay

##### New Member
that second one looks like it worked well!!! thank you so much!! this about drove me nuts

Replies
8
Views
279
Replies
1
Views
106
Replies
11
Views
458
Replies
12
Views
346
Replies
1
Views
242

1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back