Auto fill column k from one sheet to another if two cells match?

HI,
On 20 different spreadsheets, Column K has a value (scores) that needs to appear in column E or F (depending on the spreadsheet/different grades/years) when the district number and school number (columns B and D for schools) that match in each of the spreadsheets for the comparison spreadsheet I am building.

I tried lookup, but doesn't work when matching two criteria.

I think it is index and match but do not understand the syntax well enough on the help files I have accessed to write it myself.

Any and all help greatly appreciated.

for forum 4.xlsx

Gratefully,
Synthia

Hi Synthia

You are indeed correct in saying that this will work with index and match, but we need to set things up first.

For any system to work we need some rules and consistency. The lack of consistency is shown on the tab name 2012 r3sch (whereas all others have a space between r3 and sch or dis). A small thing but important.

Next, how can we distinguish between schools and districts. Easy - Districts end in "District" and schools end in "School". Except where they don't.

Can we have a helper column somewhere (it can be hidden) where we can put "Sch" or "Dis" as appropriate? To make things easier (but this is not mandatory) can we have the year on row 2 in a cell all on its own?

Subject to fixing the above items I think I can get something to work - but anyone else should feel free to offer any other suggestions

Regards
Alastair

Hi Synthia

In my office, just under the plaques stating "God is Love" and "Jesus loves you" was the text "Consistency is the next best thing to accuracy"

To get the district and school numbers consistent I have used double negatives (see columns V & W). This gets rid of leading zeros and apostrophes. Copy and paste values to the original column and set the number format to 00 or 0000.

I have inserted helper columns and rows (cunningly coloured (not colored!) so that you know that these may be hidden so as not to frighten the natives). On the schools tab I have inserted 2 columns so that all the results for both schools and districts are in column M.

The formulas then work.

I hope this saves you some work.

Let me know you need anything explaining / changing.

Regards
Alastair

Glad to have been of assistance.

Should you have further queries,feel free to PM me (as I do not always keep a good watch on the forum)

Regards
Alastair