Joining two datasets together (VBA needed?)

[dave__1]

Hi,

So I have two sets of data, I've attached a worksheet which has them both. The datasets have different intervals, but I need to drag "Colour" from Data2 into Data1 and associate it with the correct interval. Sometimes the intervals match up and sometimes they don't - which could be a problem, but as long as its within a small spread it's fine. The "Colour" column in Data1 is filled with my desired result, the source being Data2.

ManyToMany_Example.xlsx

[alansidman]

In G2 =VLOOKUP(B2,Data2!$B$2:$E$4,4,TRUE)

and copy down. In your scenario presented, this works and returns the same values as you expected.

[dave__1]

Thank you, this works well.

For future use, is the True referring to a range of values, because obviously some values aren't listed in the intervals so it searches the values as a continuous range instead of discrete?

And also, this is example data - my actual data is a lot bigger (different "shelves") - is there a way to automatically create arrays according to the Shelf, and then change the formula automatically? So the array that is searched in the vlookup is named after the same shelf that you are examining?

[alansidman]

True means it is looking for an approximate match not greater than the lookup value.

False means it will only return exact matches for the lookup value.

For further understanding, read here: http://www.techonthenet.com/excel/formulas/vlookup.php