If Function containing VLook up

[Michele]

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

 

[Arvi Laanemets]

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

 

[SteveG]

Michelle,

This worked for me.

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

HTH

Stev

 

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

 

[Arvi Laanemets]

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

 

[SteveG]

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