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 replies to "Formulas for multiple data matching"

    • BenneTan

      hi there. upload a sample excel file for us. show us your desired outcome too.

    • Shane Gindra

      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”

    • BenneTan

      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

    • Shane Gindra

      Thanks BenneTan your help has been most welcome and helpful, the formulae works like a charm.

    • BenneTan

      you’re very welcome~
      =)

Leave a Reply