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!
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!
