IO_Dork
asked on
Index Match formula problem
ok, so I have a situation where i need to use the Index, Match formula on rows and columns. However, its not working. Can anyone tell me what I am doing See attached excel example.
if my formula is looking up "Rep Code" and "State" it should be returning the registration date associated with each rep in each state.
sample-Index-Match.xlsx
if my formula is looking up "Rep Code" and "State" it should be returning the registration date associated with each rep in each state.
sample-Index-Match.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. now i better understand how index works.
I'm not getting any points for providing the solution?
The problem with the original formula is mismatched array and index range. Both Barry and my solutions would solved the problem, and I have submitted the solution prior to Barry, and yet I'm not getting anything?
The problem with the original formula is mismatched array and index range. Both Barry and my solutions would solved the problem, and I have submitted the solution prior to Barry, and yet I'm not getting anything?
Hey Harry!
I think it's better to specify all ranges to match their exact locations, which is what my suggestion does........but having said that your version certainly works for the example given
regards, barry
I think it's better to specify all ranges to match their exact locations, which is what my suggestion does........but having said that your version certainly works for the example given
regards, barry
Your Code:
=INDEX(Registrations!$A$10
See the $A$10, it should be $A$1; $A$11 should be $A$1; $D$10 should be $A$10.
Index works with rows and column numbers. With your references, you will end up be referencing Row 7 and Column 40, which is not what you need.
So fix the reference will work. Look at the highlighted cell in my attached file.
sample-Index-Match.xlsx