Index/Match and return adjacent cell or workaround
Hi folks, Can anyone help me with this little cost sheet problem?
I have an index/match formula working that returns the ‘price’ after index matching ‘supplier’ and ‘term’ on another worksheet holding my rate card. I want to use the same formula or similar (or a workaround) in the adjacent cell so that the ‘per’ and ‘min hire’ fields are automatically populated as per the rate card.
Is there a way to get the index match to look up a value and then return the value in the adjacent cell? Or the adjacent cell +1? In theory it should really index the term and supplier and then return 'price', 'per' or 'min hire' depending on my formula. Looking up adjacent cells doesn't really allow me to expand the rate card over time. Rather, it's just a little clunky.
I know it is possible to adjust the range of the index to search only certain parts of the table but this doesn't solve my problem as I need the values in the price, per and min fields to update whenever I change/update terms or suppliers via the dropdowns i.e. the index must always search the data range in its entirety.
I have my cost sheet and rate card on two separate tabs. Please see attached example.
NB – Suppliers and terms are ‘Named’ in my working sheet so they appear in list form in the data validation drop downs.
Also - cell refs might be slightly out since I have culled the example from my working sheet.