Dificulty finding the right combination for a complex lookup.

**jamesyoung79** · 05-19-2009, 11:26 PM

I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up. Any help is appreciated!

**shg** · 05-19-2009, 11:56 PM

Welcome to the forum, james.

Please take a few minutes to read the forum rules, and then edit your thread title accordingly.

**jamesyoung79** · 05-20-2009, 08:09 AM

modified the title to make it more descriptive, also I put better description of problem in the post.

**jamesyoung79** · 05-20-2009, 07:11 PM

Well after much work (2days on one formula) I finally figured it out. Although, I had to adjust my data to make it work. The chart I was referring to had to be spaced out with a row of the indexing in between each row of data. Also, the table had to be extended to the right to include a 0 before each data set and the index numbers below the data shifted one cell to the left. This is because hlookup dosn't have a way to specify which row to read from it chooses the first one automaticly(unless someone knows a way around it?). The formula to solve it is below(if anyone cares or has a similar problem).

Please Login or Register  to view this content.

**shg** · 05-20-2009, 07:31 PM

It's thoughtful of you to post back with your solution, thank you.

We'll try to do better for you next time. Looking at both your workbook and solution, though, I can't tell what the problem was, and there's no chart in it.

Even so, I think the formula simplifies to

=IF(A42="","", HLOOKUP(C42-1, OFFSET($E$17, 2 * (B42 - 1), 0, 2, 27) ,2)

... but OFFSET is volatile, so you could use

=IF(A42="","", HLOOKUP(C42-1, INDEX($E$17:$E$36, 2 * (B42 - 1)):INDEX($AE$18:$AE$36, 2 * (B42 - 1) + 2), 2) )