VLOOKUP and OFFSET

**renita8781** · 03-07-2007, 08:21 AM

My data appears as follows:

Plan Item
1 123
1 124
1 125
2 132
2 133
2 134
2 135

I want Excel to search for a particular plan and return the corresponding item. SO..I have a vlookup to do that.

Next I want to display the following items attached to that plan in adjacent columns, for example:

Plan Item (using VLOOKUP) Item 2 Item 3
1 123 124 125

For this, I plan on using an OFFSET function. I want to nest it in an IF function so I can say If the item using vlookup equals an item in the original column, return the next item (directly underneath).

This is what I have come up with:

If(Item(using Vlookup) = Item, OFFSET(Item; COLUMN()-2 *1,0; 0)

That is giving me a volatile error. Please help! What am I doing wrong???

**starguy** · 03-07-2007, 08:59 AM

Originally Posted by renita8781

My data appears as follows:

Plan Item
1 123
1 124
1 125
2 132
2 133
2 134
2 135

I want Excel to search for a particular plan and return the corresponding item. SO..I have a vlookup to do that.

Next I want to display the following items attached to that plan in adjacent columns, for example:

Plan Item (using VLOOKUP) Item 2 Item 3
1 123 124 125

For this, I plan on using an OFFSET function. I want to nest it in an IF function so I can say If the item using vlookup equals an item in the original column, return the next item (directly underneath).

This is what I have come up with:

If(Item(using Vlookup) = Item, OFFSET(Item; COLUMN()-2 *1,0; 0)

That is giving me a volatile error. Please help! What am I doing wrong???

see the attached file and let me know if it helps.