Thanks in advance for attention and help.
Each month I must look up teams based on IDs and date periods. A Vlookup doesn’t really work well nor does a pivot table do this efficiently, as I still have to visually search row by row within the results of any pivot table.
I am trying to compare two lists with each other that will give me the team value from the second list based on the client ID, period from and period to dates in the first list. I have attached a very small data sample that could represent the issue. My real data is actually quite huge.
What I want to do is to first look up the client ID in the first list (Adj Teams list) and match the client ID, its “period from” AND period to” dates in the second list (YTD IR list). When all three criteria match, I need the result to give the corresponding Team value from the YTD IR list. In essence, it’s a matrix lookup, but I can’t figure out how to do it.
I’ve heard and read about index match and combining or nesting those two functions together, but I simply don’t have a good grasp of either of those functions separately, never mind together, and I can’t seem to get anywhere on my own trying to figure out how to do this.
Would appreciate help in understanding this and perhaps a direction to take in solving this problem I encounter every month.
Thanks!
Matrix lookup
-
-
-
Re: Matrix lookup
This formula works
In Cell D4 =INDEX($G$3:$J$68,MATCH($A4,IF($H$3:$H$68=$B4,IF($I$3:$I$68=$C4,$G$3:$G$68))),4)CONFIRMED WITH CONRTOL + SHIFT + ENTER or it won't work (It's an array formula).
In your Adj Teams table, there are some combinations that result in #N/A. On closer inspection, your dates don't match any of the combinations in YTD IR list. If you don't want it to show #N/A, use:
=IFERROR(INDEX($G$3:$J$68,MATCH($A4,IF($H$3:$H$68=$B4,IF($I$3:$I$68=$C4,$G$3:$G$68))),4),"")
CONFIRMED WITH CONRTOL + SHIFT + ENTER
Dan
-
Re: Matrix lookup
You could also try either of these. A non array formula
=IFERROR(LOOKUP(2,1/(($G$4:$G$68=A4)*($H$4:$H$68=B4)*($I$4:$I$68=C4)),$J$4:$J$68),"")
=IFERROR(INDEX($J$4:$J$68,MATCH(A4&"|"&B4&"|"&C4,INDEX($G$4:$G$68&"|"&$H$5:$H$68&"|"&$I$5:$I$68,0,0),0)),"")
-
Re: Matrix lookup
This was very helpful and am so appreciative of your input. It works the way I would like it to and as an added plus, I was able to understand it.
-
Re: Matrix lookup
This also worked, but the second formula has an error in the H and I cell references. It should be row 4, not row 5.
Thank you for your input. I've had less success understanding this code, mostly because I don't use lookup so am not familiar with its syntax, but will study, along with index/match.
All of the folks who replied - your responses will work for me. Thanks!
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!