Vlookup Tricks

Good Afternoon,

Here is what I am having a hard time with, and I am hopeful someone may be able to assist!

I have a spreadsheet that has six different categories. Each category/domain has 4-6 indicators, which are numbered such as CV1, CV2, etc. This is an example of one of the categories:
(Attachment Excel2 with red and green shading)

I am hoping that there is a formula that will allow it to automatically know that we are on session 3 (and have not yet completed sessions 4-9), and to take the scores from session 3 that fall in the red (1 or 0). This would then be spit out to a "Targeted Indicators" list. So, in this example, it would be taking the indicators in session 3 that are equal to or less than 1, (which are CV3 and CV4). On my report page, it would list CV3 and CV4 and define them.

Something like this:
(Attachment Excel3 (Targeted indicators))

Currently, I am having to look at the categories on my own, and enter the indicators that need targeted (e.g. I have to look in session three, see that CV3 and CV4 are not meeting criteria, and type CV3 and CV4 into a separate table).

I am using this:
=VLOOKUP(A8,indicatorschart,2)

To tell it to look for CV3 (which is A8 on the previous sheet), use Indicatorschart to get my data, and spit out the defined indicator (column 2).

Is there a way to automate this all? I want it to look at the entire Communication chart, from sessions 1-9, know that we are currently on session 3, look at the scores in session 3, and tell me which indicators are not meeting criteria (red, score of 1 or 0). But, I don't want it to spit out multiple repeat indicators (e.g. notice that session 2 had a 0, or session 1 had a 0, ONLY on the most recent data).

Ultimately, we would like for there to be automated reports of the targeted indicators, as a follow up to our already created report that looks something like this:
(Attachment Excel5 summary report).

Can post more screenshots if needed, but am hopeful this is possible. I have tried a few different things but not had success thus far.

Thanks!

Originally Posted by sesg3c

I have a spreadsheet

but we don't have

Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Greetings,

Sorry for any confusion. Thanks in advance for your help.

The context of this is as follows:

I am an educational consultant who provides coaching and feedback to teachers and administrators.
I complete "Walkthroughs" (or observations) of classrooms across the state.
I score them in 6 different domains (which you will see on the "SCORES" tab of the spreadsheet).
These 6 domains each have 4-6 indicators in which they receive a score of 0 (not in place), 1 (sometimes in place), 2 (usually in place), or 3 (always in place).
The numbers 1-9 on the top of the "SCORES" tab are referencing the session number (example: Session 1, Session 2, etc.)
The blue "CALENDAR" tab is where it pulls these dates from (you will see on that page that we define the 9 session dates, so we know that session 1 is 9/1, session 2 is 10/1, etc.)
The "NOTES" tab is where we type observations in that are generated to the final report. There is a tab for each report (1-9), which pulls the right data from the notes and the domain charts.

Currently, I am having to enter the "INDICATORS" tab and manually type in which indicators need to be worked on. These would be the things that the teachers did not do well on (not doing well would mean a score of 0 or 1).

Ideally, I would like for EXCEL to look at each domain (Communication, Schedules, etc.), pick the latest scores (in this example it would be session 3), and tell me which scores fall at or below 1, and then VLOOKUP what those indicators are and put them in a list on the report.

Hope this makes sense, would be glad to explain further if needed.

Thanks so much for your help.