Formulas for multiple data matching

I hav a data sheet with a list of 100 venues each with diffrent room hire costs for each day of the week. I am looking to get a formulae that will pick up the costs based on the venue and day chosen.

https://www.excel-course.com/wp-content/uploads/2013/03/Receipt-Stats-2013-v21.xlsx

 

5 thoughts on “Formulas for multiple data matching”

  1. I have managed to get part of the formulae to work, but in some cases I am getting an #N/A error which I am unable to explain or identify why it is occurring. I have uploaded the spreadsheet for reference and the formulae is in Worksheet Apr Cell J2, but will be copied down as well into the other rows of column J.

    The error seems to be associated with some of the “venues” and/or with some of the “Days”

  2. hi Shane. your MATCH formula should be looking for an exact MATCH. that means you must put a 0 inside.
    =INDEX(TABLE,MATCH(F2,VENUES,0),MATCH(I2,DAYS,0))

    You will still get an #N/A in J3 because it should be “Tue”, not “Tues”. So change your list in “Dropdown Lists” A3. They should be standardised with “Venues & Fees” sheet

Leave a Reply