I have an existing Excel document that uses a lookup table to return specific values.
The working formula I use has this general structure =INDEX(MATCH([ ]&[ ],[ ]&[ ],0)).
This formula is quite long and Excel struggles to update values quickly.
To speed up my spreadsheet I have started to convert the Excel formula over to PowerPivot using DAX formula, which I can see will vastly improve overall speed and flexibility, but I need help on the journey!
The task at hand is quite small and defined. I would like the awarded person to use Excel 2013 or 2016 with the PowerPivot add-in, to present a table in the given format from the information provided in the given reference data. Please find attached an Excel file with the content in it.
There are 4 tabs to the Excel file.
Tab 1 - Contains Reference Data of the fixed value options for each field in Tab 2 and 3.
Tab 2 - Contains the lookup table that holds the values that need to be returned to Tab 4.
Tab 3 - Contains the working data file that staff will add rows to on a day-to-day basis.
Tab 4 - Shows an example of the PowerPivot Table I would like presented.
PowerPivot must return the correct 'Yield' and 'Grow Day' values, by cross referencing columns B, C, D and E of the 'Orders' Table against the look-up Table 'Yield and Grow Days'.