Vlookup at different granularity / concatenate

Hi,

Apologize if my title description is confusing but I don't know how to put into words.
Attached is the file that should explain everything

Is there any formulae, that I can use, or modify the vlookup formulae, to vlookup the description "Light bulbs" and getting the result highlighted in "yellow"?
Vlookup only return the first outcome.
I need to lookup the value on a different row, instead of every first row.

Hope I explain clear enough

Hi FDibbins,

That works perfectly.
However, what if the D1:Q1 is actually a date i.e 20/8/2015, 20/9/2015, etc?
Apologize because I was replacing the date with a number, so that it looks a bit more simple in explaining.
It is suppose in date format

Hence, when I apply your formulae, and change the D1:Q1 to date, it's all #REF...

Appreciate your help to fine-tune this...

Thanks in advance.

Regards
cks1026

The issue is not the data: I checked out the formula with dates.


Check your formula ranges and whether they are absolute or relative

Hi JohnTopley,

Can you send back the excel file?
Because I was copying FDibbins formulae and copied across, then I changes "1" to 20/8/2015, "2" to 20/9/2015, etc...it turns out to be #Ref
I didn't change anything on that formulae...

Post file to the forum.

Hi, JohnTopley,

Can you refer to my 1st post? the attachment is there?
I don't know why, but I couldn't attach the file while replying.....

Hi, JohnTopley,

Here you go...

Try change the date, and you will see #ref...

See attached

Hi JohnTopley,

I think I confuse you all, sorry for the misunderstanding.

Please look at this latest attachment.

These are just partial item where I need to lookup the outcome as highlighted in "yellow"
There are about 100-200 items which I want to lookup in similar ways.

These data are populated into pivot table.

Looking forward for all your expertise.

Thanks

Regards
cks1026

In D48

=OFFSET($D$2,MATCH($B48,$B$3:$B$44,0)+COLUMNS($A$1:A1)-1,COLUMNS($A$1:A1)-1,1,1)

Copy down and across

Hi JohnTopley,

That was freakingly brilliant, that's exactly the outcome I'm looking to have.
However, there are couple of items, where it "lookup" the wrong outcome.
Not sure what went wrong, because i'm using exactly the formulae you suggested, most of the item I can match it perfectly right.

Please refer to the attached.

Refer to Sheet3, where I lookup the outcome from the M+4 forecast, using the formulae you suggested.

Refer to row10, row11, row 46, row 47

row 10 = FFF, the outcome from the formulae is 17.30 (the right result should be 0)
row 11 = GGG, the outcome from the formulae is 14.58 (the right result should be 0)
row 46 = 1616, the outcome from the formulae is 27.30 (the right result should be 26.31)
row 47 = 1717, the outcome from the formulae is 94.45 (the right result should be 87.46)

Please advice.... very stress

Thanks

Regards
cks1026

Hi, JohnTopley,

One word for your.... AMAZING!
It works wonderfully well.

Any chance if I can ask how does this formulae actually work?
Went to google but didn't manage to get the full understanding of this formulae...

Thanks

Regards
cks1026

Hi JohnTopley,

One slight adjustment, how can I adjust this below formulae, if the outcome is #N/A?
How should I replace #N/A to 0, if any...?

=OFFSET('M+4 Forecast'!$D$7,MATCH($A2,'M+4 Forecast'!$B$8:$B$833,0)+COLUMNS('M+4 Forecast'!$A$1:A1)-1,COLUMNS('M+4 Forecast'!$A$1:A1)-1,1,1)

Thanks

Regards
cks1026

Try

=IFERROR(OFFSET('M+4 Forecast'!$D$7,MATCH($A2,'M+4 Forecast'!$B$8:$B$833,0)+COLUMNS('M+4 Forecast'!$A$1:A1)-1,COLUMNS('M+4 Forecast'!$A$1:A1)-1,1,1),0)