Index Match Question

[kevbar13]

Looking for some help with an Index-Match formula. What I have are peoples names and data in one sheet based on a month. I am looking to put that data together with other months on a different spreadsheet. The formula I have been using works for the first half or so of the worksheet, but then just stops working for the rest of the names.

Here is the formula I am using:
=INDEX('May ''17'!AG$4:AG$48,MATCH('Spring ''17'!A19,'May ''17'!A$4:A$48))

The names are a little different from month to month, so I can't just set the cell equal to the cell I am looking for and copying and pasting. The first 25 rows work, but the rest just returns a value of 0.

[loginjmor]

Hi -

It is really difficult to diagnose your problem without a sample of your spreadsheet to work with (not a screen shot or image). Please upload a sample of your spreadsheet with any sensitive information removed. To upload a file, at the bottom right corner of the reply window is a button that says Go Advanced. Push it and then scroll down the screen that appears until you find a hyper link that says Manage Attachments. Press that and use the Browse and Upload buttons to attach your file.

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[kevbar13]

Here is a simplified example of my spreadsheet. When I did this, it is now giving incorrect information, whereas in my sheet, it is correct until "David", then it just gives 0.

[loginjmor]

Hi -

I just looked at your spreadsheet. A couple things:

1. Your INDEX/MATCH formula does not need to be an Array formula (with the squiggly braces {} ). Just hit enter normally when finished editing the formula.

2. You're errors are most likely generated because you did not specify and Exact Match in your MATCH function. So just add a 0 to the end of your MATCH function so the formula looks like this:

=INDEX('May ''17'!AD$4:AD$48, MATCH('Total ''17'!A3, 'May ''17'!A$4:A$48,0))

Copy and paste this into Cell E3 of your spreadsheet and see if it works.

Hope this helps!

[kevbar13]

Thank you, that seemed to do it.

Just out of curiosity, when does it need to be an array formula?

[loginjmor]

INDEX and MATCH already work with arrays (a range of cells) as their normal arguments. You use Array Formulas when you are working with functions that normally accept one variable (one cell or number) as an argument. A very common one is the IF statement. Normally you would say IF(A1=5,"Yes","No"). So the single argument is the cell A1 and if it equals 5, the formula would return "Yes". But let's say you want to check a range of cells to see if it contains the number 5. So you could rewrite the formula as IF(A1:A10=5,"Yes","No"). In this case, since the original IF function does not normally accept an array (A1:A10) as an argument, you have to press Ctrl-Shift-Enter at the same time to engage the Array Formula functionality of Excel, and Excel will add the little curly braces {}. Then, if any of the cells in A1:A10 contains 5 the formula will return "Yes".

Make sense? There's lots of online help for Array Formulas.