# Using MATCH and INDEX function with multiple criteria

1. ## Using MATCH and INDEX function with multiple criteria

We're selling language courses that have a different price depending on when a course is taken. We're trying to build a price quote application that needs to look up a certain school and course and price driven by a start date.

How can I build a lookup function that says: pick the price of school X and course Y when the start date falls between dd/mm/yyyy and dd/mm/yyyy?

I manage to build a look up function with MATCH and INDEX when the condition of start date is exactly matched but dont know how to instruct it to match a value between a start and end date.

I have attached the simplified setup and appreciate anyone's help!

2. ## Re: Using MATCH and INDEX function with multiple criteria

Given your use of XL2007 you can use SUMIFS, eg:

``Please Login or Register  to view this content.``

For a pre XL2007 (and non-array) version you could use LOOKUP, eg:

``Please Login or Register  to view this content.``

Both assume only 1 rate will be applicable for any given combination of the variables specified in B2:B4, if not the former will aggregate and the latter will return the last price found.

3. ## Re: Using MATCH and INDEX function with multiple criteria

That was pretty close to amazing...I've been struggling for days and you reply in 5 minutes. Thank you so much!

There is one more step I need to complete to make this really work and perhaps you can help. As an exemple, a potential client who wants to study for 36 weeks and starts -in this example- on the 1st of March will go through three different price periods.

Would you have a formula solution that calculates the price correctly ie it checks the start dates (all start dates are Mondays) and then looks up and sums -in this case- 5 weeks at 140, 22 weeks at 180 and 9 weeks at 120?

That would make our lives so much easier. I've attached updated spreadsheet.

4. ## Re: Using MATCH and INDEX function with multiple criteria

In truth that will be more complex given the tiered nature of the calculation.

Is it your intention to calculate multiple "prices" simultaneously (ie 1+ set of variables at any given time) - if not you could simply calculate adjacent to your "rates" table and sum output (pretty straightforward).

Presumably all dates will be Mondays - no part weeks etc...

On a final note - the SUMIFS earlier is not an Array and as such can be entered with Enter as normal.
(SUMIFS is function added to XL2007 and beyond - alongside COUNTIFS, AVERAGEIF, AVERAGEIFS, IFERROR etc...)

5. ## Re: Using MATCH and INDEX function with multiple criteria

The result should only be one price and these are whole week prices (no broken numbers). The formula to the criteria: 24 weeks, MTSLI, General English, Start date 01/03/2010 should produce EUR 5740,00.

I'm struggling with combining "date" functionality and summing a variable number of periods (some products may only have two "price date ranges" or non at all (ie same price for the year).

Any help in how to structure this is much, much appreciated.

6. ## Re: Using MATCH and INDEX function with multiple criteria

I understand the requirement in terms of a singel cell calculation of a tiered price but what I'm getting at is whether you'll be conducting multiple of these calculations simultaneously, eg:

MTSLI, General English, Start date 01/03/2010, 24 weeks

and

MTSLI, General English, Start date 01/03/2010, 12 weeks

If you are then you need to look for a relatively elegant solution, if not you can run basic calcs adjacent to your table of rates and simply sum results

Incidentally, shouldn't the output for the first be 4120 rather than 5740 ?
ie 5 weeks @ 140 and remaining 19 weeks @ 180

If not, can you outline the math as I'm misinterpreting something I think.

7. ## Re: Using MATCH and INDEX function with multiple criteria

Just as a starter... and assuming 4120 was correct...

If we assume you want to calc in a single cell (and not run calcs adjacent to rates) then first you would be best served introducing a further column to your table which holds the "incremental" changes between rates.

Using your earlier file as the basis:

``Please Login or Register  to view this content.``

The above should generate three values which reflect the movement in value between current & prior rate: 140, 40, -60

To avoid repetitive calcs I would then be inclined to calculate the End Date in a further cell, eg:

``Please Login or Register  to view this content.``

Note this value will be the Monday following the last day of the course - this is fine - don't change!

We can now calculate the total cost of the course using the following Array:

``Please Login or Register  to view this content.``

So as you adjust the values in B2:B5 the total cost should adjust accordingly.

In reality I suspect the "incremental" rate formula will need to be adjusted to account for changes in School / Program - ie change in either from prior result is equivalent to brand new rate and thus prior rate should be ignored.

8. ## Re: Using MATCH and INDEX function with multiple criteria

I can see that this is a challenge

The database will have thousands of rows with products that can have up to 6 different date ranges in a given year. I think the column with the incremental value works fine in this specific example but not in a large database environment as rows may not necessarily be sorted correctly, no? Each row however will have a correct start and end date between which the particular price is valid.

The challenge is to find a formula logic (in different steps if need be) that cuts up the course period in week chunks matching the periods between price start and price end dates for that particular course and then summing it up. Perhaps I did not explain it well before, apologies!

Much obliged for any help in how to structure the query.

9. ## Re: Using MATCH and INDEX function with multiple criteria

Sorting data nearly always allows for greater efficiencies...

If the data is unsorted and you prefer to not use the incremental column then another single cell calculation would be:

``Please Login or Register  to view this content.``

As before the above utilises new formula in B6 (outlined in prior post)

10. ## Re: Using MATCH and INDEX function with multiple criteria

I cant get it to work (I think I may have moved around something) but I think we're close but would this formula work for different type of products with less or more time periods?

I attach updated spreadsheet with some more rows to give you an idea of real data. I really appreciate your help, this saves me a lot of time and frustration.

11. ## Re: Using MATCH and INDEX function with multiple criteria

As previously mentioned (a couple of times) B6 should contain the following:

=B4+(B5*7)

If you do that you will get your result.

12. ## Re: Using MATCH and INDEX function with multiple criteria

Apologies, in all the excitement I missed that

This formula works for this specific product but is it possible to have a formula that applies to all the rows in the table (regardless in how many price blocks they've been cut up), ie if I select MTSLI, Intensive English 25 for 6 weeks starting on 02/08/2010 that is correctly calculates 5 weeks at EUR 250 + 1 week at EUR 200 = EUR 1450?

13. ## Re: Using MATCH and INDEX function with multiple criteria

The ranges used in my formula (ie references to rows 9:11) were based on your earlier file.

Simply change the ranges used in the formula to reflect your real data.
Using your latest file that would mean changing all references to row 11 to row 16 - remember to reset the Array once you've modified

If the dimensions change all the time you may wish to consider using Dynamic Range(s), if so I'd be inclined to steer clear of using OFFSET in it's creation use INDEX instead.

14. ## Re: Using MATCH and INDEX function with multiple criteria

Correct, it would be a dynamic range in a tale with a few thousand rows. If the drop down box selects a different course and start date, the formula should not change. Can you help me set this up with the example in the spreadsheet?

15. ## Re: Using MATCH and INDEX function with multiple criteria

I think you're misinterpreting my post(s)...

To be clear...

The formula you've been provided with expects to review the rate table in it's entirety... ie all rows should be referenced.
The conditional tests within the formula will omit those rates that do not apply (either based on date, school, program)

If the rate table is fixed in size (ie you're not constantly adding / removing rows) simply reference the entire table in the formula provided (add a few excess rows if needed)

Personally speaking I would only use Dynamic Named Ranges if you were always adding / removing records such that the number of rows would increase dramatically in a short space of time - I am guessing you don't.

16. ## Re: Using MATCH and INDEX function with multiple criteria

It works, absolutely fantastic. This helps us tremendously!

One refinement that would prevent data entry error is to select the course price as well depending on the number of course weeks selected. I have now taken a short cut and created products with different course lengths (as you can see in the updated spreadsheet) but it would be much better if the price is looked up according to the number of weeks selected.

The error that can be produced now is that "General English 20 [1-7 weeks]" is selected but for a course of 20 weeks (to which corresponds a lower price).

On the tab "prices" I have started to do that and I am hoping you can help adapt the formula in such a way that the criteria "Week Min" and "Week Max" are taken into account given the number of "Weeks" have been chosen on the first tab "Calculation". I hope I've explained myself well

17. ## Re: Using MATCH and INDEX function with multiple criteria

I'm afraid I'm not sure I follow.

If the possible weeks are themselves determined by the selection in B2 why not simply modify the DV list in B5 on that basis so as to prevent incompatible selection in the first instance ?

I don't really see any value in adding yet more complexity to an already complex formula when it can be (seemingly) avoided.

For ex. if you assume you list the MIN/MAX against each combination in your Prices sheet then it follows you could use:

``Please Login or Register  to view this content.``

to determine Week start & end...
(if the naming convention of each course was 100% consistent you could even calculate from the string itself in B2)

If for sake of demo we assume the above are stored say in cells C2 & D2 on Calc tab then you can revise the DV list source for B5 to be:

``Please Login or Register  to view this content.``

at this point only the valid weeks will be available for selection

(the above is not ideal but based on setup it might be the quickest to implement)

NOTE: you would need to add weeks 37 to 52 to your WEEK range ... ie up to and incl. the MAX of Prices!E:E

18. ## Re: Using MATCH and INDEX function with multiple criteria

Yes, limiting the number of weeks in B5 to the subset of the course chosen in B2 is definitely an option but not as "tight" in terms of how clients are used to selecting a course.

They select a "General English Course" for x number of weeks starting dd/mm/yyyy and we're almost there in figuring out how we can capture and look up that data in a table

The output from a user perspective is a simplification of the dropdown value list (always good) and the removal of the weeks not matching price error.

lLet me know if I explained myself well.

19. ## Re: Using MATCH and INDEX function with multiple criteria

My apologies if my previous post was not clear. The price of a course is driven by the course length and the period during which a course is taken. Our data model captures the latter (hurray!) perfectly but not the former without a shortcut: we have defined identical courses with different course lengths. Unless we restrict the dropdown value for number of weeks taken, this will lead to erroneous data as well.

What I want to achieve (since this reflects "real" usage) is that the table query selects in addition to Location, Course Name and Period as well on the number of weeks selected.

I thought that one way of doing that was to introduce two fields in the table: one labelled "Week Min" and the other "Week Max" to indicate the range of weeks this price applies for. It will not reduce the number of rows in the table but it will simplify the customer drop down box. Instead of having to show: "General English 20 [1-7 weeks]", "General Engish 20 [8-15 weeks]"...etc. we only have to show "General English 20" and the price will be given once the client has selected the number of weeks.

Your solution to constrict the DV to those that apply to that particular course will work as well to obtain error free output but is not what clients (internal and external) are used to.

I attach the latest version of our workbook so that cell references work smoothly.

20. ## Re: Using MATCH and INDEX function with multiple criteria

Hurray, I actually solved it by simply including another >= and <= in the formula. It works like a charm.

One main data quality hurdle remains: is it possible to have a dynamic dropdown list that looks for the minimum and maximum value for number of weeks for a given course?

For example, the General English 20 course for the destination USNYCEC has a minimum number of 4 weeks and a maximum number of 23 weeks. It would be great if it were possible to have the cell B6 on the calculation tab only show numbers between 4 and 23.

I attach latest version. I appreciate it if this can be deleted from the forum after review.

21. ## Re: Using MATCH and INDEX function with multiple criteria

Originally Posted by Babylon
One main data quality hurdle remains: is it possible to have a dynamic dropdown list that looks for the minimum and maximum value for number of weeks for a given course?
see Post 17 for basic concept ... given the multi conditional nature of the lookup it makes sense to make use of AVERAGEIFS to return MIN & MAX.

I'm glad you've resolved... I'd literally just begun looking at this again prior to your post so I shan't worry now if you're happy that it's doing what it should be doing.

EDIT: disregard point re: post 17 ... it seems things have changed in so far as one given course could have multiple week durations.

22. ## Re: Using MATCH and INDEX function with multiple criteria

The following would return MIN & MAX week number for given combination of School, Course & Date

``Please Login or Register  to view this content.``

``Please Login or Register  to view this content.``

23. ## Re: Using MATCH and INDEX function with multiple criteria

I tried this for the Min value and I get a DIV/0 error...

Ignore post

25. ## Re: Using MATCH and INDEX function with multiple criteria

Re: ignore... just to clarify

AVERAGEIFS won't work now given that a given school/course/date combination can have more than one unique MIN value meaning the Average would be misleading (same holds true for MAX obviously)
(before the MIN & MAX values were constant as I recall as I believe the weeks were included in the Course name (to an extent - eg 16+))

In my prior post I failed to use Spanish delimiters - so the commas should all become semi colons - I will edit now.

26. ## Re: Using MATCH and INDEX function with multiple criteria

There's one practical step that would help us. The complete model today is with English course descriptions (which makes sense as we're using the definitions of the partner schools we work with) but to present this in an email to a client, we'd like to have the items listed in the tab "Variables" translated in Spanish (and perhaps later in other languages).

What is the best way to achieve this? Obviously we can create a new tab (or new columns in the existing Variable tab) where all phrases are translated but what is a nice way to call these translations?

27. ## Re: Using MATCH and INDEX function with multiple criteria

Again, I'm not quite sure I follow I'm afraid... can you elaborate regards:

Originally Posted by Babylon
...to present this in an email to a client, we'd like to have the items listed in the tab "Variables" translated in Spanish (and perhaps later in other languages)
what is the content of the email exactly ? The model in full ? Or are you issuing only the form element (ie Calc tab) as some sort of quote which you want translated as appropriate ?

28. ## Re: Using MATCH and INDEX function with multiple criteria

I've done everything in English but would like to show the price calculator in Spanish. I could of course simply overwrite the current texts but it would be better if I have a translation file one way or another so that is shows "Inglés Intensivo 20" instead of "Intensive English 20".

29. ## Re: Using MATCH and INDEX function with multiple criteria

I confess I'm still not sure if your intention is to mail just the results or have the calculator itself work in multiple languages ?

Regardless... you could "in theory" cater for both by doing something like:

a) creating a sheet - let's call it TCourses which stores

1. the course names (only) in English in Col A
2. their equiv. translations in Cols B onwards (one column per language)
3. the language "name" at the head of each column in Row 1 (English, Spanish etc..)

b) on your Calc tab add a further validation list at the head of the page which is source from row1 on TCourses sheet

c) modify the courses validation list so that now it references the appropriate Column from the TCourses sheet for it's source based on the language chosen in the above DV list

d) add a cell (hidden if pref.) which translates the (potentially) non English course name to it's English equivalent...
this again can be calculated based on the known language and the fact that the English courses are always Col A in TCourses sheet

e) base all calcs on the English translation cell(s) so that you can leave the Prices matrix as it is (ie in English)

I say translation cells (plural) because it follows that you would need to repeat the Courses process for other fields also (School (poss.), Accommodation, Airport)

If you could create the translation sheets (TCourses, TAccommodation etc...) and post back I will happily help you revise the Calc tab accordingly.

30. ## Re: Using MATCH and INDEX function with multiple criteria

I have created these tabs and have added the spreadsheet. I came across the difficulty (at least for me) that the words used in the course selector are used for DVs so I'd be very happy for you to have a look.

31. ## Re: Using MATCH and INDEX function with multiple criteria

OK leave this with me... I might make some alterations regards how some of the dependent validation lists are set up ... not in terms of the final output of course just in terms of config.

32. ## Re: Using MATCH and INDEX function with multiple criteria

In hindsight... rather than get too involved restructuring I figured for expediency we could leave the sheets as they were and work on the Calcs tab.

In the attached version I've made a few changes...

Col F onwards need not be visible - these are the cells that will do the translation work.

The DV lists in B5, B7 & B9 have been modified such that they now source from the translation listings on the Calc tab.

The translations themselves will update per the various "T" sheets in line with the chosen Language.

I've added some Error based approaches that basically mean if any choices are not valid (ie legacy selections based on prior language) then

a) the Calcs section won't calc

b) the erroneous cells will be highlighted accordingly for ease of review / correction

On the "T" Airport / Menu sheets some values / translations were missing - I've simply added in as place holders (eg ?) for the time being - they are in red.

Adding further languages should be as trivial as updating the various "T" sheets wiht the translations - everything thereafter will take care of itself.
(obviously you should use the same columns on each "T" sheet for the same languages)

I've demo'd this by simply adding Col C to each of the "T" sheets... the Calcs table will simply reflect the additional language in B1 dropdown and upon selection the remaining calcs will resolve themselves per their English equivalents.

Once you're happy with what you have you should think about adding some basic sheet level protection to the formula cells so they can not be overridden by accident.

33. ## Re: Using MATCH and INDEX function with multiple criteria

There are two more refinements to capture certain exceptions that happen within this dataset:
1) some courses only have certain startdates
2) some courses are only open to a certain level or require a minimum level

My question is how to structure the price table in the best possible way since it works beautifully for 95% of the courses and we're dealing here with exceptions. However, as always I'm keen to capture the data as clean as possible.

1) Can Excel read from a cell in the table called "SpecialDates" that has for instance start dates separated by commas and create a dropdownlist? This list would only be accessed if a field called "StandardDates" has a "No" value.
2) Can Excel equally construct a dropdownlist for "Levels" from a "Minimum Level" and "Maximum Level" cell? My guess is that if we associate numbers with these levels, it can be done with a Vlookup to show the "text" values.

I've created these fields in the table and created a DV called "Nivel" in the Calc sheet. I havent figured out how your translation functionality works exactly so the DV values simply come from the Spanish translation. I've added numerical values in the Variables tab if the Vlookup is the way to go.

34. ## Re: Using MATCH and INDEX function with multiple criteria

There seems to be an error by the way in the last version. If no accommodation is chosen (field is left blank) it does not calculate anything.

35. ## Re: Using MATCH and INDEX function with multiple criteria

Regards your penultimate post see if the attached does what you want... (pre-requisite flags added)

EDIT: attachment reloaded at 10:26 AM UK time (Conditional Format correction)

36. ## Re: Using MATCH and INDEX function with multiple criteria

It works beautifully but it forces me to create a new product for courses (with the same name) that have different start dates depending on whether someone is an "absolute beginner" or not.

Is it possible to have the table lookup work on the level before releasing the start dates? As an example, in the attached spreadsheet I have created new entries for "Standard General English 20" in the destination UKTBYLAL that have specific start dates if the seleceted level is "1".

37. ## Re: Using MATCH and INDEX function with multiple criteria

Not entirely sure I'm still following but perhaps the attached does what you want (?)

EDIT: file removed at 12:25 PM UK Time in respect of latter post

38. ## Re: Using MATCH and INDEX function with multiple criteria

The last attachment was still wrong - the LOOKUP won't suffice - Arrays are required.

See attached.

39. ## Re: Using MATCH and INDEX function with multiple criteria

There seems to be a minor issue with restricted start dates. Have a look at row 151 in the Prices table. It shows 15/03/2010 as an option but in the calculator, this comes up as 05/03/2010.

40. ## Re: Using MATCH and INDEX function with multiple criteria

Perhaps amending per below

``Please Login or Register  to view this content.``

41. ## Re: Using MATCH and INDEX function with multiple criteria

That seems to do the trick, more testing to follow. Thanks!

42. ## Re: Using MATCH and INDEX function with multiple criteria

There seems to be another error in the formula when a course is selected that exists for more than one level. It adds up the price for each level and then multiplies by the number of weeks. In the attached sample you'll see that 12 weeks in Torbay costs GBP 3360 but it should be half that

43. ## Re: Using MATCH and INDEX function with multiple criteria

You will need to add yet another test to the Array to differentiate the courses based on Level chosen (M3) and the Min/Max levels associated with each transaction.

Truth be told this model is pretty much "out of control" - you need to be able to maintain this going forward.... the more ifs & buts you put into it the less manageable (and inefficient) it will become.

44. ## Re: Using MATCH and INDEX function with multiple criteria

I hear you. It's pretty much the last check to have a working model that takes care of 99% of requests we'd receive. Great if you could help me build in this last tweak.

To simplify the model (but keep the functionality), how would you break this down into more maneagable bits?

45. ## Re: Using MATCH and INDEX function with multiple criteria

I was going to modify my last post but you've since replied so edit below:

Initially I was loathe to suggest redesigning what you had as given the initial requirements it wasn't really warranted....

However, n feature creeps later I would suggest that as and when (and if) you get to a stage at which your feel no further logic additions are required then it is my belief that at that point you should seriously consider how to be best rebuild those requirements into a more practical model.

Regards the last tweak, as mentioned - add the Level test into the Array - this is not really any different to your existing Week MIN/MAX test which is already being applied.

46. ## Re: Using MATCH and INDEX function with multiple criteria

I'm getting better at this...and applied it successfully What would your suggestion be (in terms of approaching this project) in rebuilding it?

47. ## Re: Using MATCH and INDEX function with multiple criteria

I'm glad you added it yourself - I was politely prodding as it's important you can tweak it (and feel comfortable doing so).

Regards redesign - that's most definitely one for "manana".

To reiterate though, it's really only worthwhile considering once you have established all requirements.... it only takes one further tweak to throw a spanner in the works of whatever re-design you had in mind.

48. ## Re: Using MATCH and INDEX function with multiple criteria

sorry...posted in wrong place

cut taj city
ag 2 3 1 4 5
js 3 2 1 5 4
cs 5 3 4 1 2

1 2 3 4 5
ag
js
cs

regards
raja

raja63,

51. ## Re: Using MATCH and INDEX function with multiple criteria

Sorry sir,

B1=CUT A2=ag B2=2 C2=3 D2=1
C1=CUT A3=js B3=3 C3=2 D2=1
D1=TAJ A4=cs B4=2 C4=5 D4=4

I need
if B6=1.....A7=ag...........B7=TAJ
C6=2.....A7=ag...........C7=CUT

regards
raja

53. ## Re: Using MATCH and INDEX function with multiple criteria

how to post sir please explain i don't know
raja

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1