Two Variable Lookup with approximate ranges

[cardosol]

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?

 

[Bernard Liengme]

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

 

[cardosol]

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.

 

[RagDyer]

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

 

[Bernard Liengme]

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

 

[cardosol]

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?

 

[RagDyer]

Have you tried my suggestion?

 

[Tyro]

I uploaded one possible solution to http://www.savefile.com/files/1394995

Tyro

 

[cardosol]

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.

 

[T. Valko]

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/2717/lookuptablets0.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.

 

[cardosol]

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!

 

[Ken Johnson]

=INDEX(Sheet2!B2:E7,MATCH(C3,Sheet2!A2:A7),5-MATCH(C5,
{250000,200000,150000,100000},-1))

Ken Johnson

 

[Ken Johnson]

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