C
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?
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?