How can find a value using two different matching criteria?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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),"")
 
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
 
Kevin,

thanks. it works.

dinesh

Kevin Vaughn said:
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),"")
Click to expand...
 
Back
Top