If Function containing VLook up

I'm trying to create an IF statement that will give me either of two values
from a table by using a vlookup. Here's a sample

Col A Col B Col C
Row1 Empl ID Year Def. %
Row2 123 2005 ???
Row3 345 2006 ???

Containing the following named table (Deferred_Perctg):

Col P Col Q Col R
Empl ID 2005-% 2006-%


Here is what I'm trying to write, but can't seem to get it to work.

If
((B2=2005),(=VLOOKUP(+A2,Deferred_Perctg,2,False)),(=VLOOKUP(+A2,Deferred_Perctg,3,False)))

What am I missing? Any help would be greatly appreciated!

Thank you,
Michele

Michelle,

This worked for me.

=IF(B2=2005,VLOOKUP(A2,Deferred_Perctg,2,FALSE),VLOOKUP(A2,Deferred_Perctg,3,FALSE))

HTH

Steve

Hi

Something like (I assume values in column C are really numbers, i.e.
percentges)
=SUMPRODUCT(--($A$2:$A$100=P2),--($B$2:$B$100=2005),$C$2:$C$100)
to calculate Q2, and
=SUMPRODUCT(--($A$2:$A$100=P2),--($B$2:$B$100=2006),$C$2:$C$100)
to calculate R2


Arvi Laanemets


"Michele" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to create an IF statement that will give me either of two
values
> from a table by using a vlookup. Here's a sample
>
> Col A Col B Col C
> Row1 Empl ID Year Def. %
> Row2 123 2005 ???
> Row3 345 2006 ???
>
> Containing the following named table (Deferred_Perctg):
>
> Col P Col Q Col R
> Empl ID 2005-% 2006-%
>
>
> Here is what I'm trying to write, but can't seem to get it to work.
>
> If
>
((B2=2005),(=VLOOKUP(+A2,Deferred_Perctg,2,False)),(=VLOOKUP(+A2,Deferred_Pe
rctg,3,False)))
>
> What am I missing? Any help would be greatly appreciated!
>
> Thank you,
> Michele

Steve,

I've tried this and keep getting a #N/A result. Below is my actual worksheet

Col A Col B Col C Col D Col E Col F
ID Def % Ttl Bonus Bonus Amt Year Def. Amt
123 ????? 15000 (1-B2)*C2 2005 B2*C2
345 ????? 15000 (1-B2)*C2 2006 B2*C2
789 ????? 15000 (1-B2)*C2 2006 B2*C2
567 ????? 15000 (1-B2)*C2 2005 B2*C2

This is my named table (DC_Percent) below:

Col I Col J Col K
ID 2005 % 2006 %
567 15% 20%
345 10% 15%
789 25% 10%
123 20% 15%

I'm trying to solve Col B with the following:

If(E2=2005,VLOOKUP(A2,DC_Percent,2,False),VLOOKUP(A2,DC_Percent,3,False))

But all I get in B2 is #n/a, when I should be getting 20%, which is needed
for the other functions in differing columns. Can you see what I'm doing
wrong?

I'm grateful for what ever help you can give?

"SteveG" wrote:

>
> Michelle,
>
> This worked for me.
>
> =IF(B2=2005,VLOOKUP(A2,Deferred_Perctg,2,FALSE),VLOOKUP(A2,Deferred_Perctg,3,FALSE))
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=491153
>
>

Hi

It looks like 2 ID columns are formatted differently - probably the one is
formatted as text, and another a numeric. Try to compare 2 visible equal
values, p.e.
=(A2=I5)
(I can't understand, are both tables on same sheet or on separate ones)
When the formula above returns False, the try to find out why.

Arvi Laanemets


"Michele" <[email protected]> wrote in message
news:[email protected]...
> Steve,
>
> I've tried this and keep getting a #N/A result. Below is my actual
worksheet
>
> Col A Col B Col C Col D Col E Col F
> ID Def % Ttl Bonus Bonus Amt Year Def. Amt
> 123 ????? 15000 (1-B2)*C2 2005 B2*C2
> 345 ????? 15000 (1-B2)*C2 2006 B2*C2
> 789 ????? 15000 (1-B2)*C2 2006 B2*C2
> 567 ????? 15000 (1-B2)*C2 2005 B2*C2
>
> This is my named table (DC_Percent) below:
>
> Col I Col J Col K
> ID 2005 % 2006 %
> 567 15% 20%
> 345 10% 15%
> 789 25% 10%
> 123 20% 15%
>
> I'm trying to solve Col B with the following:
>
> If(E2=2005,VLOOKUP(A2,DC_Percent,2,False),VLOOKUP(A2,DC_Percent,3,False))
>
> But all I get in B2 is #n/a, when I should be getting 20%, which is needed
> for the other functions in differing columns. Can you see what I'm doing
> wrong?
>
> I'm grateful for what ever help you can give?
>
> "SteveG" wrote:
>
> >
> > Michelle,
> >
> > This worked for me.
> >
> >
=IF(B2=2005,VLOOKUP(A2,Deferred_Perctg,2,FALSE),VLOOKUP(A2,Deferred_Perctg,3
,FALSE))
> >
> > HTH
> >
> > Steve
> >
> >
> > --
> > SteveG
> > ------------------------------------------------------------------------
> > SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
> > View this thread:
http://www.excelforum.com/showthread...hreadid=491153
> >
> >

Michelle,

When I reproduced your information as stated in your last post, I got the N/A error as well. I noticed in your formula that you are using the condition of the VLOOKUP as "False". What I did to correct it was change the word "False" to all caps FALSE and bingo, it worked. Give it a try.


Cheers,

Steve