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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?

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

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