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

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" wrote in message
...
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

Michelle,

This worked for me.

=IF(B2=2005,VLOOKUP(A2,Deferred_Perctg,2,FALSE),VL OOKUP(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

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),VL OOKUP(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" wrote in message
...
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),VL OOKUP(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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=491153