Extract data from third column where data in two other columns match

[redimp]

Hi, I have done a search for this not really following results so I have mocked up an Excel sheet to try and illustrate what it is I need to be able to do. My problem is that I need to extract the data for column C on 'Nearest Pupils Attending School' from Column E of 'Actual vs School Table' where the values in column A of 'Nearest Pupils Attending School' matches the values in Columns A and C on 'Actual vs School Table'.

I hope that is clear; any assistance thankfully received.

Thanks

[sktneer]

Try these array formula. Remember to confirm these formulas with Ctrl+Shift+Enter.

Please Login or Register  to view this content.

and then copy across to B4 and down until you get blank row.

And

Please Login or Register  to view this content.

and copy down until you get blank row.

[sktneer]

One more thing.......

In the previous array formulas, you don't need to input the school number manually on Nearest Pupils Attending School Sheet.

But if you are inputting the school number manually and need the formula for School Name (Col. B) and Pupils on roll for whom school is the nearest (Col. C), use the below array formulas (Ctrl+Shift+Enter)

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

then copy both the formula down.

Hope that helps.

[redimp]

Thank you. With slight amendments they seem to work OK

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Trouble is it does not seem to make any sense whatsoever. The first one (cell C4) requires an amendment to

Please Login or Register  to view this content.

to return the correct result whereas

Please Login or Register  to view this content.

works. This appears to be the reverse of what I would expect. Can you explain please because I will need to transpose these formulas into the real much bigger spreadsheet.

[sktneer]

I am not clear about where is the issue with the formula.
The correct syntax is ROWS(A$4:A4), because it returns the first record that meets the criteria as ROWS(A$4:A4)=1 and when you copy it down to next cell it becomes ROWS(A$4:A5) which is equal to 2 so returns the next record if any.

[redimp]

I am not clear about where is the issue with the formula.
The correct syntax is ROWS(A$4:A4), because it returns the first record that meets the criteria as ROWS(A$4:A4)=1 and when you copy it down to next cell it becomes ROWS(A$4:A5) which is equal to 2 so returns the next record if any.

I have amended the attached spreadsheet so that three results are returnable (attached) and put the formulas in as they appear in your first post. With the formulas as they were the result for 1112 is the result for 1111 and vice versa. The result for 1114 is correct. Usually with these it is something simple that I am missing (like a typo!)

[redimp]

An additional thing that might be relevant, I just swapped 1112 and 1111 round in Column A of Number on Pupils Attending their Nearest Schools which changed the VLOOKUP school names as expected but the pupil numbers remained unchanged; so were therefore correct.

And I have just put 1111 in cells A4 to A6 and the pupil numbers remain unchanged at:
173
378
96

[sktneer]

If you input the school number manually, please use the formula suggested in post#3.
Pls see the attached sheet.

[redimp]

This seems to work

Please Login or Register  to view this content.