Index and Match should be my best option?

[jschomisch]

I am trying to pull information from Workbook 1, and auto-fill into Workbook 2 by matching Week Number and Part Number. I almost got it with just using Index but it's counting the columns/rows (not sure honestly), but it's not 100% accurate.

Here's what I'm using now, with this formula though I need to change it weekly to pull the correct Week Number (the ones in red, I count by Columns; so I started in D and went to BE to get to 54 weeks).

=INDEX('P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$D$6:$BE$17,1,$B$1)

Workbook 1: I want to match cell B1 and the Part Numbers in Column B to Workbook 2.

Workbook 2: Based On Working Week (B1), I want to fill column C with the data from to correct working week in Workbook 1.

 

[Proulxs]

Hi,

Your index formula is looking at line 1, column number "203-2000-01". There is no such column number.
You need to change (if I understand what you are trying to fetch) your row to not be 1 but rather the same as your part number you could use match($B6;'P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$B$6:$B$17) instead. It will return the line that match that part number and tell Index to look at that row.

Then for column I'm not sure which one you want. You seem to indicate F-BE are your 54 week numbers but in your index you included D and E which are 2351-2352 which seem to also be your week? If you really want the current week I would shrink the range in index to be from F to BE and instead of having a manual week number you could put weeknum(today) and tweak if required to match your actual week number in your system. (because weeknum of dec 19th 2023 would return 51 and in our pay system would say 52 instead so I would put 1+)

 

[Fluff]

How about

=INDEX('P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$D$6:$BE$17,XMATCH(B6,'P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$B$6:$B$17),XMATCH($B$1,'P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$D$5:$BE$5))

 

[jschomisch]

Hi,

Your index formula is looking at line 1, column number "203-2000-01". There is no such column number.
You need to change (if I understand what you are trying to fetch) your row to not be 1 but rather the same as your part number you could use match($B6;'P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$B$6:$B$17) instead. It will return the line that match that part number and tell Index to look at that row.

Then for column I'm not sure which one you want. You seem to indicate F-BE are your 54 week numbers but in your index you included D and E which are 2351-2352 which seem to also be your week? If you really want the current week I would shrink the range in index to be from F to BE and instead of having a manual week number you could put weeknum(today) and tweak if required to match your actual week number in your system. (because weeknum of dec 19th 2023 would return 51 and in our pay system would say 52 instead so I would put 1+)

This worked, thank you very much!

 

[jschomisch]

How about

=INDEX('P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$D$6:$BE$17,XMATCH(B6,'P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$B$6:$B$17),XMATCH($B$1,'P:\End Of Shift Report\Robot Production\[2024 Stocking Levels - Quantity Needed.xlsx]Yearly Total'!$D$5:$BE$5))

This works as well, thank you both very much!

 

[Fluff]

Glad we could help & thanks for the feedback.