Having problem with an index match formula

Nutsy

Nutsy

New member
Joined
Jul 12, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
Pulling my hair out trying to correct this formula. Obviously I am not seeing the problem. Can someone help me out please?

I am trying to get the formula to enter a quantity in column G on the RecipeInput Page from info from the MasterPricingSheet.

Here is a copy for the work book in macro enabled workbook. I am not a genius at this just kinda youtube trained.
 

Attachments

  • test.xlsm
    112.4 KB · Views: 8
The formula should be
=IFERROR(INDEX(MasterPricingSheet!$A$2:$S$15,MATCH(D5,MasterPricingSheet!$A$2:$A$15,0),MATCH(E5,MasterPricingSheet!$A$1:$S$1,0)),"")

except you don't have "1 cup" on your Price sheet
 
Get Intersection if match Row header and Column header

Nutsy said:
formula to enter a quantity in column G on the RecipeInput Page from info from the MasterPricingSheet.
Click to expand...
With a formula that gave @fluff, you can use the following variant formulas
Code: 
=SUMPRODUCT(MasterPricingSheet!$E$2:$S$20*(MasterPricingSheet!$A$2:$A$20=D5)*(MasterPricingSheet!$E$1:$S$1=E5))
=HLOOKUP(E5;MasterPricingSheet!$A$1:$S$20;MATCH(D5;MasterPricingSheet!$A$2:$A$20)+1;FALSE)
=VLOOKUP(D5;MasterPricingSheet!$A$2:$S$20;MATCH(E5;MasterPricingSheet!$A$1:$S$1;0);0)
In the 'E' column, you need to set the Data Validation list identical to the items in the 'E1:S1' range on the MasterPricingSheet.
 

Attachments

  • nutsy-navic9687.xlsx
    103.1 KB · Views: 8
cant see it for looking at it, thanks so much!!!
 
Thankyou!!
 
You must log in or register to reply here.
Back
Top