Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 266

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
0
wrt1mea
• 2
1 Solution

Commented:
For your sample workbook, consider a formula like:
=INDEX(Sheet2!\$A\$1:\$J\$9,MATCH(Sheet1!\$A2,Sheet2!\$A\$1:\$A\$9,0),MATCH(Sheet1!B\$1,Sheet2!\$A\$1:\$J\$1,0))
0

Commented:
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.
0

Author Commented:
Thanks for the help and the feedback...

Look for more questions from me.
0

Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.