Log In
Register
Home
New Member
May 8, 2020
Offline
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]))
My various attempts are in column s, t and v.
Please help!!!!!
See Answer
July 16, 2010
Offline
Hi Christina,
Welcome to our forum! Thanks for sharing your file. It's great to see you're using dynamic arrays 🙂
The difficulty you're having is caused by the need for an exact match on the Manager (Team ID) and an approximate match on the Total Points Range. You can use FILTER to return a list of Agents based on an exact match to the Manager (Team ID), then use MATCH to return an approximate match on the Total Points Range.
You'll see the formulas on the Commission Detail sheet:
Cell W2: =INDEX(FILTER(Levels[Agent],Levels[Team]='Commission Detail'!A2), MATCH(G2, FILTER(Levels[Total Points Range],Levels[Team]='Commission Detail'!A2)))
More on the FILTER function here.
Or with LET:
Cell X2: =LET(
Agents, FILTER(Levels[Agent],Levels[Team]='Commission Detail'!A2),
TPR, FILTER(Levels[Total Points Range],Levels[Team]='Commission Detail'!A2),
INDEX(Agents,MATCH(G2,TPR)))
More on the LET Function here.
Mynda
P.S. I changed the formula in column A of the Commission Detail sheet so that it spills based on the Sub Agent dynamic array, saving you having to update it 😉 You cannot write the formulas above so that they spill though.
Answers Post
New Member
May 8, 2020
Offline
Thank you!!! I just saw this response in my email. I love Excel and how there are so many ways to get the answer you need.
Trusted Members
Moderators
November 1, 2018
Offline
FWIW, since your table appears to be sorted by Team and then points value, you could also use:
=IFERROR(LOOKUP(2,1/(Levels[Team]=A2)/(Levels[Total Points Range]<=G2),Levels[Agent]),0)
if you didn't have access to those new functions.
1 Guest(s)
