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?