How can find a value using two different matching criteria?

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


"Dinesh" wrote:

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

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

Pete,

Thanks a bunch. It works perfectly.

"Pete" wrote:

> 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" wrote:

> 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
>
>
> "Dinesh" wrote:
>
> > 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
> >
> >

You're welcome.
--
Kevin Vaughn


"Dinesh" wrote:

> Kevin,
>
> thanks. it works.
>
> dinesh
>
> "Kevin Vaughn" wrote:
>
> > 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
> >
> >
> > "Dinesh" wrote:
> >
> > > 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
> > >
> > >

Glad to hear it, thanks for feeding back.

Pete