# Need help referencing data from another tab and inserting the result in a single cell

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.

• 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. Jul 23, 2021 at 0:47

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.