Pulling data from another sheet based on the value in a column.

[rossmcf]

Hello all,

I have what I assume is a simple problem, however I am struggling to wrap my head around the formula needed to do this.

My spreadsheet has 2 sheets, Sheet1 is the main 'database' and contains multiple information columns, Sheet2 is a smaller set of data that shares a few columns from Sheet 1.

Example:

Sheet1
Institute, Name, Surname; DOB; ID; Date enrolled; Subject...etc..

Sheet2
Name, Surname, ID.

My idea is that I want to input an ID value into Sheet2 (Column C), and have the remaining data rows (Name, surname etc) pulled from Sheet1.

It's been a while since I played around with Excel so I am not sure what the best way to do this is. I was trying some IF statements without much luck.

For example, under the Name column in Sheet2 I tried the following (Column C is the 'ID' column in Sheet2.):
=IF(C2='Sheet1'!F2,'Sheet1'!A2,"Error")

This works for a single value and does the trick, however I need this to function over a range so that any 'ID' I feed into a row of Sheet2 Column C will pull the data from Sheet1.

From what I have seen online I may need to use a FILTER, INDEX or VLOOKUP function.

Please let me know if you need me to explain this better.

Thanks!

[AliGW]

Welcome to the forum.

INDEX MATCH in Sheet 2 A2, and copy down:

=IFERROR(INDEX(Sheet1!B:B,MATCH(C2,Sheet1!F:F,0)),"Error")

[rossmcf]

Thanks Ali!! That's exactly what I needed!

I knew someone would have an easy answer! I think it's time I do an Excel refresher course, do you have any in mind? I've got the basics down but would like to learn more advanced functions.

So glad I found this forum, I'll definitely be back in the future with more questions.

Cheers!

[AliGW]

Just hang out around here - that's how I learnt. Set yourself a project.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.