Search within another sheet

I am trying to collate student marks. Because of the different systems, all the lists are different, so I can’t just copy and paste between sheets. However, there is one sheet (“MARKS”) which contains the correct current list. What I need to do is to copy the marks from another sheet to this one.

Something like this:

  1. For each student, the first cell in that row will be an ID number; search in the next sheet (“Other”) for that ID number.
  2. Copy the value from the last cell in that row in “Other” to the relevent cell in the current row of “MARKS”.

For example, suppose I’m in row 49, and cell A49 of sheet “MARKS” contains “12345678”. Seaching in sheet “Other” finds 12345678 in row 53. So I need to copy K53 to E49.

Is there an easy way to do this?

1 Like

“What I need to do is to copy the marks from another sheet to this one.” I assume “the other sheet” is ‘$Other’ and “this one” is the sheet ‘$MARKS’.

You have to define and pass the ranges for looking up the foreign keys and getting the relevant field(s). If the IDs in ‘Other’ are, say, in $Other.$A$2:$A$20001 and the relevant marks in $Other.$K$2:$K$20001 you might use:

=INDEX($Other.$K$2:$K$20001;MATCH($A49;$Other.$A$2:$A$20001;0))

in the cell, say, K49 of ‘Marks’ to poll the vallue you want.

This is rather raw. I would prefer to keep or prepare a bit of meta info and then use OFFSET() to create and pass range references.

For further advice I need to know more details. Best would be a reduced and depersonalised but relevant example file.