Display Multiple Inventory Locations in Google Sheets

1
votes

Wondering if I can use =Query function to look up a range of SKU`s and return the results of where each SKU is and what location & qty it holds? (or something else that can)

I have created a spreadsheet to show my RAW Data & then expected result

https://docs.google.com/spreadsheets/d/1eaTwgvZ29SJylD13MWdCcd-slOhdVrurF-did1oOXxY/edit?usp=sharing

Appreciate any help

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

1 Answers

0
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(F3:F, A:B, 2, 0)))

0

or with QTY:

=ARRAYFORMULA(IFNA(VLOOKUP(F3:F, A:C, {2, 3}, 0)))

enter image description here

UPDATE:

=ARRAYFORMULA(IFNA({VLOOKUP(H3:H, A:C, 2, 0), VLOOKUP(VLOOKUP(ROW(A3:A), 
 IF(H3:H<>"", {ROW(A3:A), H3:H}), 2, 1)&COUNTIFS(VLOOKUP(ROW(A3:A), 
 IF(H3:H<>"", {ROW(A3:A), H3:H}), 2, 1), VLOOKUP(ROW(A3:A), 
 IF(H3:H<>"", {ROW(A3:A), H3:H}), 2, 1), ROW(N3:N), "<="&ROW(N3:N)), 
 {Locations!A2:A&COUNTIFS(Locations!A2:A, Locations!A2:A, 
 ROW(Locations!A2:A), "<="&ROW(Locations!A2:A)), Locations!D2:D, Locations!C2:C}, 
 {3, 2}, 0), VLOOKUP(H3:H, A:C, 3, 0)}))

0