vlookup multiple returns excel

Question

I am working with a data set of students information at a local university.

I used a VLOOKUP, using the IDs of the students as the unique identifier. But my problem is that the VLOOKUP only requires one row of information and half of the students in the study have more than 5 lines each. How can I make EXCEL return multiple lines?

Answer 1

You need to short duplicate entries into two separate column, and look in to both column.

I did that if you have logical though, you can do it.

Answer 2

VLookup will return the first row matching or closely matching the condition depending on the last boolean function parameter. It is assumed that the ID is unique and not repeated several times in the table you are searching in.

As far as I understand the issue, either you find a way to make a unique key for the lookup table so a key loking like 'ID + another column' formatted in a unique way such as 'ID - Other ID' or you will have to use VBA to retrieve all the rows matching a certain non unique condition.

If the job at hand is small, or VBA cannot be considered, and it is acceptable to work it out manually using auto Filters to do the matching/retrieving could help.

Answer 3

If I understand correctly your issue: have you tried playing with VLOOKUP([range_lookup] = TRUE/FALSE) ?

If not, I'd guess your best bet would be to Create a unique, combined ID for each student. Try posting a reproducible example :)