Formulas for multiple data matching


March 13th, 2013 by Shane Gindra

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.

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

 

5 Responses to “Formulas for multiple data matching”

  1. BenneTan BenneTan Says:

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

  2. Shane Gindra Says:

    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”

  3. BenneTan BenneTan Says:

    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

  4. Shane Gindra Says:

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

  5. BenneTan BenneTan Says:

    you’re very welcome~
    =)

Leave a Reply

You must be logged in to post a comment.

s2Member®