Formula to Index and Match across mutiple worksheets not working

[Tryin2Excel]

Attached is a workbook in Excel 2003. On Mid Sch-DHS & Partners Report worksheet is an Index and Match formula in D3 & D4 that is not working. The intent is to have a working formula in the remaining cells in D column.

The formula in D3 {=INDEX(INDIRECT("'"&'Drop Down'!$K$2:$K$13&"'!$G$3:$G$202"),MATCH($B3,INDIRECT("'"&'Drop Down'!$K$2:$K$13&"'!$F$3:$F$202"),0))} is to look for the school, identified in B3, in column F of the month worksheets and display the corresponding grade level from column G of the month worksheet. The name of the month worksheets are listed on the Drop Down worksheet in column K and referenced in the formula. The formula in D3 & D4 will display the grade level when it is found in the first month, July, but it will not display the grade level when it is found in any other month. The formula returns a #N/A when a school is not located and I am uncertain if there is a custom format to remove the #N/A.

Some of the individuals who will be using this workbook have Excel 2010 and some still have 2003.

Thank you in advance for your assistance.

[dilipandey]

Hi Tryin2Excel,

welcome to the forum.

On Mid Sch-DHS & Partners Report worksheet is an Index and Match formula in D3 & D4 that is not working.

What result you want to see in d3 and d4 ?


Regards,
DILIPandey
<click on below * if this helps>

[Tryin2Excel]

To help there is an example in the workbook I set up you can look at. If you look at the ‘Aug’ worksheet, Monroe Grade (School) is listed in F3 and the Grade Level 7 is listed in G3. On the MID SCH-DHS & Partners Report 7 should be listed in D3. But it is not. The formula only seems to work if the school and grade level are listed in the first month, July. All other months the grade level will not be displayed. I want the formula to look at the school name listed in column B of the MID SCH-DHS & Partners Report and then to search column F all of the month worksheets for the first occurrence of that school. Once the school is found to look at the grade level in column G of the corresponding row and display it in column D of the MID SCH-DHS & Partners Report. I hope that provides better clarification for you. Thank you for responding.

[dilipandey]

Hi Tryin2Excel,

In sheet "MID SCH-DHS & Partners Report", see the yellow cell where I just simply pressed enter instead of ctrl shift enter and now I can see the output as 7

MFMC v3.zip

Regards,
DILIPandey
<click on below * if this helps>

[Tryin2Excel]

Yes, thank you that does work. But if you carry the formula down it does not work. Go to the formula in D4 and change it from an array formula to a “regular” formula using enter as you indicated. Then go to any of the month tabs, other than July, and in column C select Mid Sch, in column F select Linus Pauling, in column G select one of the Mid Sch Grade Levels and move to the next cell. When you look at D4 of the MID SCH-DHS & Partners Report the grade level you selected from Linus Pauling is probably not listed, or at least in the report I tested it in it is not. My test still displays #N/A. When I initially found this formula, I believe on a thread on this forum, it did not indicate the formula was an array formula. When I initially tried using the formula did not work so I thought to try it as an array formula where the formula only worked if the school was listed in the first month. I am open to any other help you or anyone else might be able to provide. Have I written the formula wrong or am I using the wrong formula for what I need? Again thank you for your help.

[dilipandey]

thank you that does work. But if you carry the formula down it does not work.

because next data is not there.

Now since your formula is working now for one cell and for rest - its your formula only so you will be in better position to correct that. One hint I already give above


Regards,
DILIPandey
<click on below * if this helps>

[Tryin2Excel]

Thank you for your response. Please indulge me and perform the following test to see if I am correct in saying the formula will not work even when the formula is changed by using Enter instead of Ctrl+Shirt+Enter (Array).

1. In D4 of the MID SCH-DHS & Partners Report change the formula from an array formula to a “regular” formula using Enter as you indicated.
2. Go to any of the month tabs, other than July, and in column C select Mid Sch.
3. In column F select Linus Pauling.
4. In column G select 5-6 and move to the next cell.

When you look at D4 of the MID SCH-DHS & Partners Report, 5-6 is probably not listed, or at least in the report I tested it in it is not. My test still displays #N/A. If the formula worked correctly it should display 5-6 in this example. I should not have to go back to the formula after I have entered the school and “fix” the formula. Does that make sense?

Thank you for continuing to work on this.

[FDibbins]

Hi, coming in fresh on this, so a few questions...

I cab see that you are trying to return data based on a school name and a date, but I dont see where you are getting the date from?

[Tryin2Excel]

Thank you so much for looking at this. I am trying to have the formula return the grade level, not a date, from column G when the name of the school is found in column F of the same row. It might appear as a date given the 7-8 or 5-6, this is just representing that it could be offered to the 7th and 8th grade or 5th and 6th. The grade level only needs to be displayed the one time and pull from the first time the school is found in any of the month worksheets. The grade level the program is being offered to will remain the same throughout the school year. Thanks again.

[FDibbins]

aahh OK. And yes I know you were asking for the grade level, but couldnt figure out how the month (sheet) name came into it.

[Tryin2Excel]

I thought I should provide you with some additional information on this workbook. There are several other reports that are attached to this workbook that I did not include given it would have made the workbook to large. One of the reports lists the monthly totals and another is broken out into three reporting periods. All of the different reports needed lead me to setting it up with monthly worksheet. But to pull the data needed for the reports it requires formulas that are more complex, at least to me.

[Tryin2Excel]

Recently I can across a post in a different forum that provided a formula that searched all of the month worksheets and returned the grade level the first time the school name was listed in any of the month worksheets.

The formula is an array formula Ctrl+Shirt+Enter
=IFERROR(VLOOPUP($B6,INDIRECT("'"&INDEX(MONTHS,MATCH(TRUE,COUNTIFS(INDIRECT("'"&MONTHS&"'!$F$3:$F$302"),$6)>0,0))&"'!$F$3:$G$302"),2,0),"")

[FDibbins]

Im really sorry I did not get back to you on this, it must have slipped through the cracks

I am happy, however, that you managed to resolve this

[Tryin2Excel]

Thank you, yes I was happy to figure out a solution. And no problem about not getting back on this post, there are probably many posts you respond to each day.

[FDibbins]

Ywes, and when the forum is busy, a post can get pushed down 3 or 4 pages really quick - then just gets lost Thats when you need to "bump" the thread by posting a reminder