Link to home
Start Free TrialLog in
Avatar of IO_Dork
IO_DorkFlag for United States of America

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
Avatar of Harry Lee
Harry Lee
Flag of Canada image

The reason it's not working is because you have setup wrong range references.

Your Code:
=INDEX(Registrations!$A$10:$BE$57,MATCH(B3,Registrations!$A$11:$A$57,0),MATCH(C3,Registrations!$D$10:$BE$10,0))

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
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IO_Dork

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?
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