How to return value in column adjacent to item in Sheet2 and return results in Sheet1?

Hi all,

In Sheet 1 there are two columns ie. B:B=SKU and Batch no., C:C=Quantity
In Sheet 2 each SKU is divided into 4 columns: 1-SKU and Batch no, 2-Quantity, 3-Value, 4-Unit price.

Problem A: I need to get the quantity adjacent to each 'SKU and Batch no' item in Sheet2 and return the results in C3 of Sheet1 and be able to copy down to the rest of C:C. I tried INDEX(Sheet2!B3:Y16, MATCH(Sheet1!B3, Sheet2!B3:Y16, 0) +1) without success. INDEX(Sheet2!C:C, MATCH(Sheet1!B3, Sheet2!B:B, 0)) would work but is limited as it cannot be copied down.
Problem B: Can the resulting formula be tweaked to also fetch Value and Unit price from Sheet2?

Note A: Columns in Sheet1 will extend down and each four-column-SKU in Sheet2 will extend indefinitely to the right as items are received.
Note B: Quantities in Sheet2 gets updated as new items are received and recorded in a separate 'Good received' Sheet3.

I'm at wits end and need help. Thank you.

Hi aswethink and welcome to the forum.

The data on sheet 2 is wrong. You shouldn't use merged cells and it should go down columns in rows instead of across. See the attached with a small data restructure and a VLookup answer.
VLookup after data format.xlsx

Hi aswethink,

Another problem with your structure is in row 2. In a table of Excel data you should have different words for each column head. You repeat the words like "QTY", "Value", etc across in row 2. Excel has lots of problems with this type of repeated column heads.

I don't like the way that SKU number and batch number are combined on sheet 1.

EITHER they need to be separated into two columns OR we need CLEAR and ABSOLUTE rules. This formula works, for your sample SOLELY on the basis that the batches always begin with the word "Batch". If they don't, then they almost certainly will HAVE to be in a separate column:

=INDEX(INDEX(Sheet2!$B$3:$Y$11,,MATCH(LEFT(B3,SEARCH("Batch",B3)-2),Sheet2!$B$1:$Y$1,0)+1),MATCH(B3,INDEX(Sheet2!$B$3:$Y$11,,MATCH(LEFT(B3,SEARCH("Batch",B3)-2),Sheet2!$B$1:$Y$1,0)),0))

Hi Glenn,

What I did wrong was oversimplify the sample file as I tried to follow the forum rules. In the actual file, SKU number and batch number have their own columns, but concatenated to try and match data in Sheet2, where SKU and Batch nos. are listed under a single column. I've rewritten the sample file to better reflect the actual file without formulas. Please see attached.

Hi Glenn,

I made it work in the updated sample file. I separated the SKU and Batch Numbers into two columns as you suggested, but still used a third, helper column to concatenate the two for use as reference.

= INDEX(INDEX(FIFO!$L$3:$AI$9,,MATCH(B3,FIFO!$L$1:$AI$1,0)+1),MATCH(E3,INDEX(FIFO!$L$3:$AI$9,,MATCH(B3,FIFO!$L$1:$AI$1,0)),0))

FIFO=Sheet2 in the original

MANY THANKS to both you and MarvinP!