Trying to cross reference data when there isn't a table, Vlookup unsuitable

Occasional Contributor

Hi, due to the nature of the information required, one of the sheets isn't a table, but a set of merged cells etc.

 

I am aware that I have to unmerge cells to do the formula, and then merge them after formulas made to prevent the #SPILL! error. I always try things for a few hours first on my own before I ask for help. So I figured that part out.

 

Vlookup only works with certain structured cells, and I think Index Match might have the same issue as it doesn't come from a table.

 

I am trying to avoid macro in case the people I then give the files to don't have compatible software.

 

Here are screenshots of the sheets

Screenshot 2022-05-17 201003.jpgScreenshot 2022-05-17 202012.jpgSo the Student Registration Sheet is in a non traditional format due to the amount of data and type of data I require that needs to fit in a printable area,

So in the BLUE section I have a dropdown list with A, B, C, D

In the YELLOW section I have student names

 

I initially thought that

=IF('Student Details'!G3="A", 'Student Details'!C4, "")

was the solution, however I realised, that if I used this, it would leave lines blank in the list in image 2 until it found another registration with A, instead of filling it in line by line.

 

So I need it to fill in names in picture two in the Yellow section, but I will have different tables for A, B, C

 

Am I making any sense? I have ADHD dyslexia and other learning difficulties and disorders, but really trying.

 

Or can I use this table that's fetching data from the first table to only select A or B using the header optins somehow?Screenshot 2022-05-17 210815.jpg

1 Reply

Hi @ShearaKLC,

 

You are trying to create a list based on criteria. Here is a link to an article that explains how to do that. I've used it lots myself. It's a little complicated, but it works great. Hope you get it to work for you!

 

https://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

Dominique