Vlookup with dynamic lookup range

Hello all, any help gratefully received on the following please. I have a workbook with 2 worksheets. Sheet 2 uses a vlookup to search for values in Sheet 1. The problem is that for every one column the vlookup moves to the right in Sheet 2, the lookup range needs to change in Sheet 1 by 3 columns. I can't work out how to combine vlookup with index or match of offset to do this. In the attachment, cell D6 (almonds) in Sheet 2 needs to lookup with value for E3 (almonds) in sheet EB. The value for Almonds for each type of product is not in the same row in sheet EB

Firstly, thank you for spending the time and forgive me for anonymising one step too far. Here are the answers to your questions.
1. In Sheet EB, Almonds has 2 values I'll need (i) 42 for % and (ii) 16.8 for grams. Not every group of 3 shaded columns (which represents one product) has Almonds.
2. In Sheet EB, cell D7 should have a title "Kind Bars" which matches one of the Hlookup categories in Sheet 2
3. In summary, in Sheet 2 for the first product (Kind Bars) Ill need the two values 42 and 16.8 from sheet EB. For the second product on Sheet 2, there are no values, for the 3rd product, the values would be 6 and 3.

Hope that's clearer.
Thanks

Sheet 2 is used to populate a chart with % values. I want the 16.8 in a new Sheet 3 to create a second graph, using grams instead of %

Yes that's exactly right. I can send a more complete sheet if it would help you

Here is new sheet

I played around with this some more but still can't get it working. On Sheet 1, I added a helper row at the top to return the address of the top left hand cell of the lookup array on sheet EnergyBars. I can get it to return a full file path including filename and cell address, but then this doesn't work inside the vlookup. There must be a simpler way of doing this and I would still be grateful for any help please. Thanks!

In Sheet1

in B4

Please Login or Register  to view this content.

in Sheet2

in B4

Please Login or Register  to view this content.

In both Sheets, copy across and down

thank you DFLAK and John. You were both v helpful. I'll now work through the formula so I understand the nested index and match functions. I hope this serves for anyone else with the same question

I've used the formula successfully on Sheets 1 and 2 now. Thank you again.

One issue remains: when a particular manufacturer on sheet Energy Bars has more than one product (eg Loving Earth, in cells AN2 and BI2, Sheet 2 only picks up the leftmost occurence of the product when using the Index Match formula on Sheet 1 or Sheet 2.

Please could you help me extend the formula so it checks TWO values on sheet Energy Bar? It needs to check in Row 2, and Row 5 so I get a unique product on Sheets 1 and 2.

Thank you again.

Not solving your request but changed the search to use wildcard

in B4

=IFERROR(INDEX(EnergyBars!$D$2:$CQ$100,MATCH("*" & $A4 &"*",INDEX(EnergyBars!$D$2:$CQ$100,0,MATCH(Sheet1!B$1,EnergyBars!$D$2:$CR$2,0)),0),MATCH(Sheet1!B$1,EnergyBars!$D$2:$CR$2,0)+1),"")


=IFERROR(INDEX(EnergyBars!$D$2:$CQ$100,MATCH("*" & $A4 &"*",INDEX(EnergyBars!$D$2:$CQ$100,0,MATCH(Sheet1!B$1,EnergyBars!$D$2:$CR$2,0)),0),MATCH(Sheet1!B$1,EnergyBars!$D$2:$CR$2,0)+2),"")

And "Loving Earth" was "Loving Earth " in "Energy Bars" (extra blank) so NO match was found.

I have not thought about the duplicate but the simplest answer is to label them "Loving Earth_1" and "Loving Earth_2"