Using Index/Match functions when a field is a range of #'s
Posted by Rusty Smigler on October 25, 2001 10:56 AM
=INDEX(Rates,MATCH(K2,Term),MATCH(K3,Price,1))
Price
$0-$399 $4-$799 $8-$1000
Term: 12 0.0916 0.0898 0.0895
14 0.0797 0.0782 0.0778
16 0.0709 0.0694 0.0690
18 0.0640 0.0626 0.0622
Hi All,
This is the formula that I am using. It works fine when the price is one number ie. 200. I have also tried to use the match_type function, but it seems to want to round up or down. I am trying to find out a way to allow it to accept the price ranges as a field and make this work. The overall idea is that the sales reps will enter the product price and term then the price will be multiplied by the rate in the chart to figure out the monthly PMT. The chart is really large so this is just a sample. Thanks
Posted by mseyf on October 25, 2001 11:32 AM
one thing you might try is to create a hidden row underneath your price ranges with a formula to extract the highest price from the range, and use the result of the formulas as your 'Price' range. Your formula could be something like:
=VALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)-1))
(change A1 to the cell with the price range).
HTH
Mark
Posted by Aladin Akyurek on October 25, 2001 11:39 AM
Rusty,
Have a look at
http://www.mrexcel.com/tip021.shtml
that describes how to use INDEX plus MATCH vs VLOOKUP.
If still in doubt, post a bit more elaborate description of your problem.
Aladin
========
Posted by Rusty Smigler on October 25, 2001 12:38 PM
Hi Mark,
You blew that one right by me. So if-
=INDEX(Rates,MATCH(K2,Term),MATCH(K3,Price,1))
Rates=the finance rates
Price=the price ranges row field
Term=months column field
K2=term amount entered by the user
k3=product price entered by the user
Price
Then what are right, len, and the "-" supposed to represent and where is the data entered. I know that this is a little convoluted at this point, but thanks in advance. If you have any easier ways or formulas for this problem, I'm all ears.
I could send the doc. via email, if it would be easier.
Posted by Rusty Smigler on October 25, 2001 2:39 PM
This is what Aladin came up with: Great Job
As you can see, I modified the price list in order to apply the MATCH function. Check if this modification gives indeed desired results.
I used
=VLOOKUP(J3,RATES,MATCH(J4,PList)+1)
to fetch the appropriate rate.
RATES is a named dynamic range, meaning that you can add any number of records to or delete records from RATES (This table does not include C3:H3). You can see this name only thru Insert|Name|Define.
PList stands for C3:H3 (which you can also see via the Name Box).
I didn't include any check for Term < 12. If appropriate, you can change the VLOOKUP formula to
=IF(J3>11,VLOOKUP(J3,RATES,MATCH(J4,PList)+1),"")
