wrt1mea
asked on
I need a correct excel formula
See the attached file. I am trying to build upon an Index Match formula that I got on exp exch yestereday but cant get the syntax right.
What I am trying to do is match agent names on sheet 1 with their respective counts on sheet 2. For illustration purposes, these two sheets are in the same book, but in the production report, these sheets are across the network and in different files.
5-4-11.xlsx
What I am trying to do is match agent names on sheet 1 with their respective counts on sheet 2. For illustration purposes, these two sheets are in the same book, but in the production report, these sheets are across the network and in different files.
5-4-11.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
Thanks for the help and the feedback...
Look for more questions from me.
Look for more questions from me.
You also need to restrict the range of cells that the MATCH looks at to a single row or column. And the length of this row or column needs to match the data array in the INDEX function.
I added $ in the appropriate places so you could copy the formula across and down (starting with cell B2).
For the real case where the sheets are in different workbooks, please have both of them open. You should then build the formula by actually selecting the ranges in the other workbook. If those ranges are large, it would be acceptable to select a smaller range (with the same starting point) and correct the address after you know the formula is working; I'd do it at the same time as adding in the $ to force an absolute reference to the ranges being searched.
Once you are happy with the formula as edited, you should close the source workbook. Excel will then build the path part of the formula for you automatically.