Retry: VLOOKUP nested in IF Statement
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 |
Retry: VLOOKUP nested in IF Statement
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 "Michele" wrote: 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 |
Retry: VLOOKUP nested in IF Statement
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" wrote in message ... 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 |
Retry: VLOOKUP nested in IF Statement
You're a lifesaver. That was it on the nose. Thanks!
"bpeltzer" wrote: 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 "Michele" wrote: 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 |
All times are GMT +1. The time now is 01:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com