Retry: VLOOKUP nested in IF Statement

[Michele]

Hi!

Below is my actual worksheet (I'm solving the ????? with the If statement):

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.

Thanks!
Michele

 

[bpeltzer]

Usually, these #N/As come up when the value you're looking up is numeric and
the field you're looking in is text (or vice-versa). To determine which is
which, try applying the comma style (select the cell(s) and click the big
comma on the formatting toolbar). Did they cell(s) change to show two
decimals? If so it's numeric, if not it's text.
If you get different results from your two tables, that's the issue. (If
not, it's something else, and you can skip the rest). If you need to lookup
text in a column of numbers, you can change the lookup to something like
vlookup(value(a2),...
If you need to lookup up text in a column of numbers, you can use something
like vlookup(trim(a2),...). If it's not important to maintain the current
type, you can change the text to numbers by clicking an empty cell, and
copying (ctrl + c). Then select your text and Edit > Paste Special, select
Values and click OK.
HTH. --Bruce

 

[Bob Phillips]

Michele,

Are you sure that some of the values don't have spaces in them? Typing the
values in and it works fine for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Michele]

You're a lifesaver. That was it on the nose. Thanks!