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

cderhay

New Member
Joined
May 7, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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]))

Please help!!!
 

Attachments

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

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 7, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 7, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
that second one looks like it worked well!!! thank you so much!! this about drove me nuts
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,010
Members
416,892
Latest member
Bensch

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top