I need a correct excel formula

Posted on 2011-05-04
Last Modified: 2012-05-11
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.
Question by:wrt1mea
    LVL 80

    Accepted Solution

    For your sample workbook, consider a formula like:
    LVL 80

    Expert Comment

    I believe you had the two MATCH bits in the wrong order. The first one needs to find the row number and the second one the column number. I also added a 0 as the third parameter to one of the MATCH functions.

    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.
    LVL 1

    Author Closing Comment

    Thanks for the help and the feedback...

    Look for more questions from me.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now