UNSOLVED - [XL2010] vlookup value, then offset x rows down and x columns to the right

**Armitage2k** · 10-31-2012, 11:50 PM

Hi,

attached a sample of a workbook I am currently fighting with.

On the summary sheet from R5 downwards I need to lookup data indirectly from the Sep12 worksheet. I do this by first checking if there is any value in column F respectively in B19 on Sep12 sheet, then proceed to lookup the value from F4 on the Sep12 sheet and return the value which is either 3, 6 or 9 columns to the right.
This works perfectly fine if the looked up value is unique among the lookup-table on Sep12, but now I am dealing with survey questions which are answered with values of "Yes" and "No" which are given multiple multiple times in the table, therefore the formula only returns me the correlative data of the first "Yes" value it finds.

Now, what I would like to achieve is instead of looking up the "Yes/No", lookup the UNIQUE question, then just go down one / two rows and x columns to the right to display the needed value. My formula however doesnt seem to work...

Please Login or Register  to view this content.

BTW, I need to be able to define how many columns to the right the formula should go, as I need to lookup different figures.

Would much appreciate if someone could have a look.
Thanks,

A2k

Armitage2k - offset sample.xlsx

**FDibbins** · 10-31-2012, 11:56 PM

without looking at you're file, just a quick suggestion. would it help if you combined the unique question and teh yes/no and then did a search based on that?

**Armitage2k** · 11-01-2012, 12:06 AM

I was thinking about that, but firstly the format of the source format is not always consistent as it is getting updated frequently (questions are added, some answers are added/removed, etc.) as well I dont want to tinker with the format of the source sheet as this is something some people are looking at frequently. Most importantly though, the question is one row above the answers, basically meaning that if you want to combine them, you have to make a formula crossing over several rows. If the form gets updated, I have to updated all the formulas manually as well... so, no good.

Thanks though,
A2k

**FDibbins** · 11-01-2012, 12:02 PM

looking at you're layout, i have to say i would have done quite a bit differently. i would have put the answer in a cell next to the question, and would have indicated the answer options in the question. that way, a summary would be far easier to create. as it is, sometimes you're answer is the next 2 lines below the question, other times its 3 and even up to 6.

unless there is something in a column or cell to tell excel where 1 question starts and where the next 1 starts, how would it know to look down 2, 3 or 6 lines for the answers?

I would suggest you rethink the layout of your tables

**Armitage2k** · 11-01-2012, 11:00 PM

again, this format comes from my companies worldwide headquarter and is in use in over 4500 properties. I dont really have any influence in changing this and must deal with what it got.

so, formatting aside, does anybody have any clue why formula doesnt work?

EDIT:

I have some success with this formula, but still not quite there. I placed this formula on the summary sheet cells G22 to R22:

Please Login or Register  to view this content.

this formula does return the value I am looking for, but ONLY if I enter the formula in the same column on the summary sheet as the data is in on the Sep12 sheet. That means, that if the score of the reporting period is in column E on the Sep12 sheet, I MUST place the formula in column E on the summary sheet in order to get the value. If I move the formula to column F on the summary, it will display the value from column F of the Sep12 sheet, and so on...

Now, to put it simple: Is there a formula that allows me to manually choose how many columns to the right and how many rows down i want it to go, and then return this specific value? New sample below.

Armitage2k - offset sample (rev1).xlsx

Thanks so much,
A2k