Using multiple OFFSET & MATCH formulas beyond the 1st instance

[TimX81]

Hi, I'm having trouble using OFFSET and MATCH to find matches beyond the first instance of a match. The root of the problem is the match_type selection. Between selecting a match_type of 1 and 0, I'll either match the wrong value, or return an error. The solution is probably very simple, so I apologize for the litany of reference material below. I run Excel 2007.

My worksheet is supposed to check whether a promotion is active by looking for a matching tag. Assuming there is a match, the formula needs to calculate the promotional price x # of months in the promotion.

In general, the current worksheet layout is as follows:
-Tags for standard price elements running along a header row (e.g., StandardMonthlyPrice)
-An array of values below the header row with prices
-A section for promotional elements running along the far right of that header row
-An array of values below the far right of the header row, where up to 12 different promotions can be entered, where a tag like StandardMonthlyPrice can be entered in one column, with columns for promotional price, promo. start month, and promo. end month to adjust the calculation of monthly prices

An excerpt from my formula is below. To establish whether the tag at FM3 matches a promotion, the formula checks whether that tag exists in SL8, and then has offsets to gather the price in SM8, the start month in SP8, and the end month in SQ8. The formula repeats with offsets spaced 12 columns apart thereafter, since additional promotions can be entered in each row:

=IF(ISNA(MATCH(FM$3,$A8:$SL8,0)),0,OFFSET($A8,0,SUM(MATCH(FM$3,$A8:$SL8,0)))*(OFFSET($A8,0,SUM(MATCH(FM$3,$A8:$SL8,0),4))+1-OFFSET($A8,0,SUM(MATCH(FM$3,$A8:$SL8,0),3))))+IF(ISNA(MATCH(FM$3,$SM8:$SX8,0)),0,OFFSET($A8,0,SUM(MATCH(FM$3,$A8:$SL8,0),12))*(OFFSET($A8,0,SUM(MATCH(FM$3,$A8:$SL8,0),12+4))+1-OFFSET($A8,0,SUM(MATCH(FM$3,$A8:$SL8,0),12+3))))+[...]

Let's say that the tag in FM3 is matched not at SL8, but 12 columns to the right in SX8 (expressed as the "12" offset above). Meanwhile, SL8 is non-blank, with a different tag entered. The formula will return an #N/A! error.

I'll be very grateful for any suggestions toward reworking the formula. Thank you!

 

[Rijnsent]

Hi Tim,
my general remark would be: cut that big formula into some more practical bits (using some helper columns). Secondly: if you would be able to post (a dummy version of) your Excel, that would make things easier to understand and explain.
Cheers,
Koen

 

[Sandeep Warrier]

Hi & welcome to the Board!

As Rijnsent said, it'll be more helpful if you could provide some sample data along with expected results.

My signature has a few methods of posting sample data.

 

[jasonb75]

If I understand your logic correctly, there should only be 1 match somewhere in row 8?

In which case all you need is

=IF(COUNTIF(8:8,FM$3),INDEX(8:8,MATCH(FM$3,8:8,0)+1)*(INDEX(8:8,MATCH(FM$3,8:8,0)+5)+1-INDEX(8:8,MATCH(FM$3,8:8,0)+4)),0)

That omits the need for an additional section of formula for each block of 12 columns.

 

[TimX81]

Thanks for your replies! It would be difficult to illustrate the problem without a full Excel 2007 attachment, given the number of columns involved.

I think the simplest solution is to replicate the reference formula 12 times, so the formula doesn't 'trip' over any promotions matched to the left of the correct reference. It's going to require lots of processing power, but it'll get the job done and still be several thousand columns shy of Excel 2007's limit.

Thanks again!

 

[jasonb75]

Tim, I assume from your original description that you have multiple blocks of same format data to process. i.e. wherever the match is found, it's always (1 cell to the right)*(5 cells to right)+1-(4cells to right).

Should there only be a single match for the tag in the row, or multiple matches? If multiple, should they all be calculated, or is there an additional criteria to choose the correct one?

 

[TimX81]

Hi jasonb75,
There could be multiple matches, and all should be calculated. The formula for matches is always (1 cell to the right)*(5 cells to right)+1-(4cells to right), so the result is [monthly charge]*[promotion end month]+1-[promotion beginning month], calculating the total value over the life of a promotion.

So let's say that the 1st promo is a 6-month discount on a subscription to a health club, the 2nd promo is a discount on a country club membership for months 1-12, and the 3rd promo is a discount on a country club membership for months 13-24. Ideally, if both FM3 and SL8 match as "CountryClubPromo," the formula would know to skip the 1st promo, and then calculate promos 2 and 3. At present, the formula returns #N/A! because the 1st promo does not match CountryClubPromo."

 

[jasonb75]

Ok, that's starting to make more sense now, just need to figure out the relative layout of the rest of the data.

Can you give the locations of the tags for the first 3 promotions.

What is the relevance of FM3 and SL8? I first thought that SL8 was the first tag, but your reference to "skip the first promo" suggests that is not the case.

If needed for clarity is it possible for you to create a dummy xlsx workbook with 2 or 3 rows of "john doe" data and upload it to a fileshare site?

 

[jasonb75]

I'm going to take a wild guess on a solution, if nothing else, this will give you something to try.

Paste the formula into the cell, then press Shift Ctrl + Enter, not just Enter, to confirm the formula as an array, if done correctly when you look at the formula bar the formula will be inclosed in curly braces.

=SUM(IF(SL8:INDEX(8:8,MATCH(9.9E+307,8:8))=FM3,SM8:INDEX(8:8,MATCH(9.9E+307,8:8)+1)*(SQ8:INDEX(8:8,MATCH(9.9E+307,8:8)+5)+1-SP8:INDEX(8:8,MATCH(9.9E+307,8:8)+4))))

 

[TimX81]

jasonb75, thanks - I haven't been able to get your suggestion to work so far.

The file URL is below - sorry for the delay in getting this uploaded:
http://www.box.net/shared/s78v7titcf