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!