Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
I have a two variable look up problem.
In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
This is what Help says about MATCH
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
Hi Bernard,
This goes for the full table. If the Value is between 100k and 150k, it still will not return the correct value. If I enter 130,000 in cell A2, and my match function is -1, it returns a #N/A value or if it is 1, it returns the value for 100k. replacing the data with the minimum value would not be a solution as this data is used by others as well. "Bernard Liengme" wrote: This is what Help says about MATCH If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
Try this in B1 to E1:
1 101000 151000 201000 With this formula: =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (C5,Sheet2!A1:E1)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "cardosol" wrote in message ... Hi Bernard, This goes for the full table. If the Value is between 100k and 150k, it still will not return the correct value. If I enter 130,000 in cell A2, and my match function is -1, it returns a #N/A value or if it is 1, it returns the value for 100k. replacing the data with the minimum value would not be a solution as this data is used by others as well. "Bernard Liengme" wrote: This is what Help says about MATCH If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
I put you data on Sheet1 and use
=INDEX(A1:E7,MATCH(B10,A1:A7,0),MATCH(C10,A1:E1,1) ) B10 had NM and C10 had 123,456, my formula returned 10 With B10= NY and C10 = 234,567, I get 50 Email my private address (remove TRUENORTH.), and I will send you a file. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... Hi Bernard, This goes for the full table. If the Value is between 100k and 150k, it still will not return the correct value. If I enter 130,000 in cell A2, and my match function is -1, it returns a #N/A value or if it is 1, it returns the value for 100k. replacing the data with the minimum value would not be a solution as this data is used by others as well. "Bernard Liengme" wrote: This is what Help says about MATCH If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
But your results of 10 and 50 are incorrect. The amounts in the headers are
loan amounts UP TO 100,000 UP TO 150,000 so based on your data points of NM and 123,456, excel should return 20 because 123,456 is greater than 100k but less than 150K. How would I write a formula for this to return the correct value? "Bernard Liengme" wrote: I put you data on Sheet1 and use =INDEX(A1:E7,MATCH(B10,A1:A7,0),MATCH(C10,A1:E1,1) ) B10 had NM and C10 had 123,456, my formula returned 10 With B10= NY and C10 = 234,567, I get 50 Email my private address (remove TRUENORTH.), and I will send you a file. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... Hi Bernard, This goes for the full table. If the Value is between 100k and 150k, it still will not return the correct value. If I enter 130,000 in cell A2, and my match function is -1, it returns a #N/A value or if it is 1, it returns the value for 100k. replacing the data with the minimum value would not be a solution as this data is used by others as well. "Bernard Liengme" wrote: This is what Help says about MATCH If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
Have you tried my suggestion?
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "cardosol" wrote in message ... But your results of 10 and 50 are incorrect. The amounts in the headers are loan amounts UP TO 100,000 UP TO 150,000 so based on your data points of NM and 123,456, excel should return 20 because 123,456 is greater than 100k but less than 150K. How would I write a formula for this to return the correct value? "Bernard Liengme" wrote: I put you data on Sheet1 and use =INDEX(A1:E7,MATCH(B10,A1:A7,0),MATCH(C10,A1:E1,1) ) B10 had NM and C10 had 123,456, my formula returned 10 With B10= NY and C10 = 234,567, I get 50 Email my private address (remove TRUENORTH.), and I will send you a file. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... Hi Bernard, This goes for the full table. If the Value is between 100k and 150k, it still will not return the correct value. If I enter 130,000 in cell A2, and my match function is -1, it returns a #N/A value or if it is 1, it returns the value for 100k. replacing the data with the minimum value would not be a solution as this data is used by others as well. "Bernard Liengme" wrote: This is what Help says about MATCH If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
I uploaded one possible solution to http://www.savefile.com/files/1394995
Tyro "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
Hi RD,
Yes, your suggestion works but will not make sense for business users. Since the value returned is the maximum closing costs per state and loan amount, it would not make sense for the data table to have minimum loan amounts as headers. "RagDyer" wrote: Have you tried my suggestion? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "cardosol" wrote in message ... But your results of 10 and 50 are incorrect. The amounts in the headers are loan amounts UP TO 100,000 UP TO 150,000 so based on your data points of NM and 123,456, excel should return 20 because 123,456 is greater than 100k but less than 150K. How would I write a formula for this to return the correct value? "Bernard Liengme" wrote: I put you data on Sheet1 and use =INDEX(A1:E7,MATCH(B10,A1:A7,0),MATCH(C10,A1:E1,1) ) B10 had NM and C10 had 123,456, my formula returned 10 With B10= NY and C10 = 234,567, I get 50 Email my private address (remove TRUENORTH.), and I will send you a file. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... Hi Bernard, This goes for the full table. If the Value is between 100k and 150k, it still will not return the correct value. If I enter 130,000 in cell A2, and my match function is -1, it returns a #N/A value or if it is 1, it returns the value for 100k. replacing the data with the minimum value would not be a solution as this data is used by others as well. "Bernard Liengme" wrote: This is what Help says about MATCH If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
If you don't want to reorder your table then add another column header that
shows the *full* range of the interval. Something like this: http://img409.imageshack.us/img409/2...uptablets0.jpg The formula refers to this other column header. If you don't want to see this other column header set the font color to white. Note: any value in A2 200,000 will return the value from the last column of the table. -- Biff Microsoft Excel MVP "cardosol" wrote in message ... But your results of 10 and 50 are incorrect. The amounts in the headers are loan amounts UP TO 100,000 UP TO 150,000 so based on your data points of NM and 123,456, excel should return 20 because 123,456 is greater than 100k but less than 150K. How would I write a formula for this to return the correct value? "Bernard Liengme" wrote: I put you data on Sheet1 and use =INDEX(A1:E7,MATCH(B10,A1:A7,0),MATCH(C10,A1:E1,1) ) B10 had NM and C10 had 123,456, my formula returned 10 With B10= NY and C10 = 234,567, I get 50 Email my private address (remove TRUENORTH.), and I will send you a file. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... Hi Bernard, This goes for the full table. If the Value is between 100k and 150k, it still will not return the correct value. If I enter 130,000 in cell A2, and my match function is -1, it returns a #N/A value or if it is 1, it returns the value for 100k. replacing the data with the minimum value would not be a solution as this data is used by others as well. "Bernard Liengme" wrote: This is what Help says about MATCH If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. In the 100,000 150,000 200,000 ... there is no value less than or equal to 90,000 Why not replace 100,000 by 0 ? or some other number as fits your case best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
Thanks Tyro. I made the change to include a from and to field for the
information to calculate. This will seem to work. I apologize to others for being difficult. :) Everyone was a big help! "Tyro" wrote: I uploaded one possible solution to http://www.savefile.com/files/1394995 Tyro "cardosol" wrote in message ... I have a two variable look up problem. In Cell A1 I have a state to enter. In cell A2, I have a loan amount. I need cell A3 to give me the closing costs based on the two values above. My table looks similar to this: 100,000 150,000 200,000 250,000 CA 1.00 2.00 3.00 4.00 NM 10.00 20.00 50.00 100.00 WA 500.00 1,000.00 1,200.00 5,000.00 NY 10.00 20.00 50.00 100.00 NC 2.00 4.00 6.00 8.00 WY 10.00 20.00 50.00 100.00 The first row is loan amounts UP TO. So upto 100k. If I enter a value less than 100k, I should still return 1 if my state is CA. I am using the formula =INDEX(Sheet2!A1:E7,MATCH(C3,Sheet2!A1:A7,0),MATCH (Sheet1!C5,Sheet2!A1:E1,1)) which is working great if my loan amount is exactly 100k but not 90. I tried to change the match function to either 1 or -1 but it will not return the correct values. What am I doing wrong or what should I change? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
=INDEX(Sheet2!B2:E7,MATCH(C3,Sheet2!A2:A7),5-MATCH(C5,
{250000,200000,150000,100000},-1)) Ken Johnson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Variable Lookup with approximate ranges
Correction. I left the 0 out of the 1st MATCH.
=INDEX(Sheet2!B2:E7,MATCH(C3,Sheet2!A2:A7,0),5-MATCH(C5, {250000,200000,150000,100000},-1)) Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Ranges | Excel Worksheet Functions | |||
variable reference ranges | Excel Discussion (Misc queries) | |||
Sorts on variable ranges | Excel Worksheet Functions | |||
Variable ranges | Excel Worksheet Functions | |||
Sum Variable Ranges | Excel Worksheet Functions |