0

I am having trouble getting this to work in google sheets. I want E2 from tab "Prepack Dashboard" to reference the data in A2 and B2, match those to A2 and B2 in tab "Inventory Minimums", then take the data from C2 in tab "Inventory Minimums" and fill it into E2 on tab "Prepack Dashboard". It makes more sense when you see the sheet. Please help.

https://docs.google.com/spreadsheets/d/1QOucUBgU9S5qMtB176zO3UELWoSjAkNNIQHt8ZTh7JM/edit

1
  • Hi and welcome. Please don't make volunteers open your spreadsheet to find out the purpose of Column E, or how it relates to the related from Inventory Minimums. Would you please describe your problem in words, include a screenshot or two if that would be helpful, and include an example of how an successful outcome would appear. You describe yourself an an easily confused dummy - would you also summarise your research prior to asking this question.
    – Tedinoz
    Jul 23, 2021 at 0:47

1 Answer 1

0

Enter this formula in sheet "Prepack Dashboard", cell E2.

=ArrayFormula(IF(isblank($A2:$A),"",VLOOKUP(A2:A&B2:B,{'Inventory Minimums'!$A$2:$A&'Inventory Minimums'!$B$2:$B, 'Inventory Minimums'!C2:C},2,0)))

Breakdown
VLOOKUP looking up an array (the part in curly braces)

  • VLOOKUP(A2:A&B2:B,{'Inventory Minimums'!$A$2:$A&'Inventory Minimums'!$B$2:$B, 'Inventory Minimums'!C2:C},2,0)
  • this takes the combined value of the "Strain" and "Inventory Type" from columns A&B of "Prepack Dashboard", and looks that up in the combined columns A & B of "Inventory Minimums"
  • when it finds the lookup value, it returns the value from Column C (which is the minimum quantity).

ARRAYFORMULA

  • This allows the formula to be automatically copied down for as many rows as there is data. Without this, VLOOKUP would have had to be copied manually. Even if ARRAYFORMULA was applied to the VLOOKUP, it would not automatically copy it down the column.

IF and ISBLANK

  • This is the "trick" that enables ARAYFORMULA to copy the VLOOKUP formula down the column.
  • This passes an array to ARRAYFORMULA which it can useuses to copy down the column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.