How can find a value using two different matching criteria?

[Dinesh]

Dear Friends,

Col c Col d Formula
Lease Term Company # 60 2 3%
36 3 7%
48 4 3%
60 5 2%
12 6 20%
24 7 FALSE
36 8 FALSE
48 10 5%
60 9 FALSE

Residual Table


Company #
1 2 7
4 3 8
5 6 9
10

Col b
Lease Term Residual %
12 10% 20% 9%
24 5% 9% 6%
36 4% 7% 3%
48 3% 5% 2%
60 2% 3% 1%


=IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$26,VLOOKUP(C9,$B$30:$E$34,3,0))))))))

How can I get a residual % by comaring first Company # and then by months
from Residual % Table?

the above formula worked except for Company # 7.8.9. The reason, it allows
only 7 time to use IF or Vlookup function which is already used by col C and
D.

Is there any other way to create a formula. the result has to be one column.

Thanks a lot for help.

dinesh

 

[Kevin Vaughn]

From your formul
=IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$26,VLOOKUP(C9,$B$30:$E$34,3,0))))))))
it appears that if D9 is in the range c23:d26 you want to do the vlookup, so
the following formula appears to work:

=IF(COUNTIF($C$23:$D$26,D9) > 0,VLOOKUP(C9,$B$30:$E$34,2,FALSE),"")

 

[Pete]

I think this will give you what you want:

=IF(OR(D9=$C$23,D9=$C$24,D9=$C$25),VLOOKUP(C9,$B$30:$E$34,2,0),IF(OR(D9=$D$23,D9=$D$24,D9=$D$25,D9=$D$26),VLOOKUP(C9,$B$30:$E$34,3,0),VLOOKUP(C9,$B$30:$E$34,4,0)))

If D9 is 1, 4 or 5 the residual% is the first column, if D9 is 2, 3, 6
or 10 the residual% is the middle column, otherwise it is the last
column, and in each case the value of C9 determines which row of the
table is used.

Hope this helps.

Pete

 

[Dinesh]

Pete,

Thanks a bunch. It works perfectly.

 

[Dinesh]

Kevin,

thanks. it works.

dinesh

From your formula
=IF(D9=$C$23,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$24,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$C$25,VLOOKUP(C9,$B$30:$E$34,2,0),IF(D9=$D$23,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$24,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$25,VLOOKUP(C9,$B$30:$E$34,3,0),IF(D9=$D$26,VLOOKUP(C9,$B$30:$E$34,3,0))))))))
it appears that if D9 is in the range c23:d26 you want to do the vlookup, so
the following formula appears to work:

=IF(COUNTIF($C$23:$D$26,D9) > 0,VLOOKUP(C9,$B$30:$E$34,2,FALSE),"")

 

[Kevin Vaughn]

You're welcome.
--
Kevin Vaughn

Kevin,

thanks. it works.

dinesh

 

[Pete]

Glad to hear it, thanks for feeding back.

Pete