Pull info from 1 worksheet into another - not a one-to-one relationship

**heideho** · 03-05-2009, 07:16 PM

So, I have one worksheet with 238 rows and another with 163 rows. I want to pull information into the sheet with 238 rows whenever there is a match to a record in the 163 rows. I tried VLookup, but as soon as it gets to a record in the 238 that doesn't exist in the 163 it gives errrors and stops. I've looked at =offset and =offset(match) but I'm not finding anything that deals with the situation of not always being a one-to-one (or even one-to many) relationship. Ultimately I want to pull the Category & Project fields into the records matching on IDNumb in Sheet 1 but Sheet 1 has more records than Sheet2

example
Sheet 1 Sheet 2
IDNumb Descrip IDNumb Category Project

**JBeaucaire** · 03-05-2009, 07:22 PM

Go ahead and mock up a full example of what you're trying to do, and don't simplify down too far. Make sure enough data and enough "sample results" are shown to clearly demonstrate your need. This sound pretty straightforward, but people frequently leave out things until they put together a good visual example, so post it up here for us to look at.

GO ADVANCED on your next post and click on the paperclip to attach your workbook.

**heideho** · 03-06-2009, 11:21 AM

Thanks for the help. I'm new to this forum (in case you couldn't tell). I've cut down my worksheets to a smaller data set but you'll see what I'm trying to do. I'm trying to get the category and overall project codes into Sheet1. Any suggestions would be greatly appreciated. I figure there has to be a way. Ultimately, the records in Sheet 1 that don't have a match, I'll need to add to the sheet 2 Master list and add a category and overall project to them

**JBeaucaire** · 03-06-2009, 12:17 PM

I shaded the cells I added the 3 formulas into. Does this work for you?

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

**heideho** · 03-06-2009, 12:27 PM

THANK YOU, THANK YOU, THANK YOU -- you have no idea how much time I have spent on trying to figure this out. Others in the office told me it couldn't be done and that didn't make sense to me. I just happened on this forum yesterday afternoon and have marked it as a favorite now.

I don't know if you are a rocket scientist but you definitely ROCK!

**JBeaucaire** · 03-06-2009, 01:10 PM

Hey, here's a followup. I remembered you said you needed to gather the ERROR items together, so here's the sheet again, this time with a second section that will gather the error items together in a single table.

You can copy the new items as a group, EDIT > PASTE SPECIAL > VALUES at the bottom of your MasterSPRList and fill in the missing data. This would also instantly remove them from the "new items" list.

Just to show them that it could be done. Check out this version and see how I did it.