Spreadsheet help please! Xlookup I think

[Passmethecrisps]

Hi,
I am hoping someone will help me here. I have two spreadsheets I was hoping to merge - it seemed simple at the time. . .

One is a list of students with a unique identifier and all their subjects. The other is a list of students, all their subjects and the exam arrangements where they have them.

I want a complete list of all students and all subjects with the relevant arrangements pulled over. I thought it would be an Xlookup but I cannot get it working at all.

I will try to get images just now

Try using INDEX combined with MATCH. Exceljet explains how to do it.

exceljet.net/index-and-match

I find the INDEX MATCH combination works better than VLOOKUP or HLOOKUP

Good luck!

Thank you. I am not sure that gives me what I need. It’s so frustrating as I feel like a vlookup with a combined lookup value and multiple column index numbers should work. But it isn’t!

Is the unique identifier on both spreadsheets? If so I would use a VLOOKUP. What's going wrong?

Second index and match. Saves having to sort and define columns or rows.

I need to pull over multiple values from multiple column identifiers.

I can get it working with one single identifier and one single column. Once I make it multiples is gives me #value or #spill

Not sure I'm understanding, if you want multiple results in various columns you need a new formula each time I think.
If you're bringing in some data then trying to use that as the identifier for another column, you need to copy and paste just the values to remove the formula.

Apologies if I'm getting the wrong end of the stick. Screenshots would help if you can.

Give us a dummy screenshot of what you want the end product to be - I'm sure we can sort it out

Thank you. I am getting so annoyed with myself. I will try screen shots but I am working on a different device so will see what I can do.

Basically I need to use candidate number, name, subject level and name as an identifier to pull through the assessment arrangements which may be multiple. All stored on one row though.

Ok, what I'd do is concatenate the unique student reference with the subject on both students to get a single unique reference per student per subject (eg AZ1234Laboratory Science) and then use that reference as the key for an INDEX MATCH formula on each column.

Thank you. I am taking a break and doing a mundane data entry task just now to step back. It seemed so straightforward in my head but nothing has worked.

I will try the index match method and if that doesn’t work I give up and we will have two different timetables

Message before re uploaded as it wasn’t quite obscured enough.

I think there's two issues:
the match needs to be made on multiple values - basically name and subject are the values in common on both spreadsheets. So I would do as @EvilRingahBitch* suggests and make a single column for matching in both spreadsheets, as long as names etc are formatted identically in both

• there are potentially multiple matches in spreadsheet 2 for each row in spreadsheet 1, i.e. student Jane Smith takes Maths and has an assessment on Wednesday and another on Friday.

If every student on spreadsheet 1 appears at least once in spreadsheet 2, I would use spreadsheet 2 as the base spreadsheet and add the additional columns from spreadsheet 1, but my guess is that some students don't appear in spreadsheet 2. So an ideal list would look a bit like:

1234 Jane Smith Maths Wednesday
1234 Jane Smith Maths Friday
1234 Jane Smith French
4321 John Smith Maths Wednesday
4321 John Smith Physics
5678 Janet Smith Biology
8765 James Smith PE

I would personally use Power Query to solve this but that's too much of a learning curve for one problem, I think! I feel a bit like a pivot table might be the answer here but I'm not sure how yet.

Let me know if we've understood the problem correctly, OP!

Getting there!!

When I set this up I had my original list of all students with their complete list of subjects. I copied this for the assessment details to be added presuming that we would leave those without arrangements in. That didn’t happen and I got a totally different spreadsheet back with kids missing. Hence trying to pull them together now. Bleh

I did start playing with power query. And pivots. But when you have a time sensitive problem to solve is not the time to be playing is it? I must get my head around both of those things but not now maybe

I got it working!! With some help from a pal. Used a concat to make a single unique key as mentioned above. Then it was plain sailing with the vlookup.

Thank you for al your help and ideas