Hlookup is driving me to distraction

Hi
I have a workbook that has pricing breaks per purchased quantity and I want to look across another sheet within the work book and find the unique number then populate the value from the cell in the adjacent column. I had originaly been trying Hlookup but can't get that to look at the adjacent column.

On the exaple workbook I would like the formula to be created in cell D2 (Unique number concatonated with quantity) on the "Lookup Sheet", to look across the "Data sheet" and return the value in cell J6 on the Data sheet, and then copy that down and across the other unique numbers


I have tried index,match but I am really struggling with this, any help would be greatly appreciated as I am at the loosing sleep stage...

Regards
John

Thank you for the reply.
I tried this and it returns a value of zero which I thought was a decimal point issue so I have formated the cell to ten decimal placed but the value is still zero.

Are we saying that the solution offered could work in a later version of ie: excel 2007 or 2010?

Try this non-array formula:

D2: =SUMIF(Data!$C$2:$O$6,C2,Data!$D$2:$P$6)


Normally Enter

Hi Bebo021999.
Sorry for the late response, I got sidetracked with other issues.
Thank you for this, it has been great, I really do not understand array formulas and would like to understand how this formula works because I have a second sheet where the value that I am trying to extract is always five colums along from my unique number (counting the unique number as column 1).
Would it be possible to use this formula in this way?

I would also like to thank all of you that responded to my question. I will try each of your suggestions as well.

I am amazed at the quick responses and the helpfulness of you all and from so far afield!

Regards
John

Hi
I have attached a workbook for my revised requirement,a similar problem to my original question except I want to extract data from a column further along the sheet rather than the one next to the unique number. Again I have two sheets one contains the data and the second (lookup) is the sheet that I wish to populate with extracts from the data sheet.
I want to find the unique number in the data sheet and extract the value from the fifth column to the right of the unique number (counting the unique number as column one, this would be cell BH2 in the datasheet). Starting in cell AS2 on the lookup sheet.
This currently contains an adaptation of the array formula that worked from my original question, but as you can see I really do not know what I am doing, so again any help would be greatly appreciated.

Regards
John

Hi
Lookup sheet values is as follow
I would like in as2, Bh2 from the data sheet
ax2 to have b02
bc2 to have bv2
bh2 to have cc2
bm2 to have cj2
br2 to have cq2
then i would want to copy the formula down the sheet to populate the other unique number cells.

I hope this helps.

Regards
John

Hi
I ran this on a machine running 2007 and it is an excellent solution.
Thank you so much for this and also the breakdown of how the formulas work, I now understand a little more about this amazing product.

Regards
John