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?