need formula to give result for next occurrence in a list

the overall concept that im trying to achieve is my available sheet are the units i need to take out of those warehouse locations and redistribute to the warehouses on the need sheet. Into column D of the Available sheet.

obviously VLOOKUP will just give me the first instance on the need list for all available units instead of different warehouses. I've tried versions of match and index and i cannot get it to work for my spreadsheet.

the lines are always qty of 1, being that even if i have 4 units of the same part coming out of warehouse 453, on the spreadsheet they will display as 4 lines of qty 1 never 1 line with qty 4. so that 4 warehouses on the need sheet can be allocated to each line. (also on the need sheet, if a warehouse requires qty3 that will be 3 lines of qty1)

in some cases there are more units to take out than what i can redistribute, i expect those lines to have an N/A type result.

on the example images attachment there is expected result which I manually put together to show what im trying to get.

(unfortunately the only link that attaches converts it into google sheets but i need this in excel)

TIA