looking at two sheets & show matching records

[PW6780]

Hello,

I have attached my spreadsheet.

In sheet 1, i have a list of fictitious schools with addresses, in sheet 2, i have a list of fictitious students with addresses

What i require is a formula that sits in column F of sheet 2, that looks at the students 'postcode' then searches for the same postcode in the schools list in sheet 1.

If any of the postcodes match then the name of the school, would be required to show in the column F, for that student.

In my attached spreadsheet, the only match is 'Ben' and 'School 8' so whats required is a formula that would return 'School 8' to the cell F7 on sheet 2.

Many Thanks in advance.

[Sindhus]

Try this formula in Column F..

=IFERROR(INDEX(Schools!A:A,MATCH(Students.!E2,Schools!E:E,0)),"")

[PW6780]

Many Thanks for the response, formula works great....

Now i've applied the formula to live data, i've noticed that some postcode can match but the school & student can have still have different 'address 1' details.

As a double check is it possible to have a 2nd formula in column G, that would work on the same principle but match the 'Address 1' fields instead of postcode this time.

Many Thanks

[benishiryo]

hi PW6780, you may then try this array formula. copy this formula & paste inside the formula bar of F2:
=IFERROR(INDEX(Schools!$A$2:$A$12,MATCH(B2&E2,Schools!$B$2:$B$12&Schools!$E$2:$E$12,0)),"")

press CTRL + SHIFT + ENTER to confirm.

[Sindhus]

@PW6780: For matching address, you can just change the cell references accordingly.

=IFERROR(INDEX(Schools!A:A,MATCH(Students.!B2,Schools!B:B,0)),"")