|
If/Then formula help
A B C D E
1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
try in B1
=Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
bj,
Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
Looks like a type-o..........replace the # with $
=Vlookup(A1,$D$1:$E$3,2,false) Vaya con Dios, Chuck, CABGx3 "TMF in MN" wrote in message ... bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
Note the # sign at #E$3 should be a $ sign.
As in $E$3 Gord Dibben Excel MVP On Mon, 13 Jun 2005 14:41:03 -0700, TMF in MN wrote: bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) |
I still can't get this to work. My reference cell is actually in column E
and my 'table' to look up column E data is in column P,Q,R. Column P should match the data in column E and insert the value (name) entered in Column R. The reference chart (P,Q,R) is a separate table but on the same worksheet. The fomula I adapted from bj's response is: =VLOOKUP(E6,$P$6:$P$152,3,FALSE) I get a N/A result in Row 6 and #REF! when I copy the formula down. What am I doing wrong? Is the column Q messing me up? Does my reference data HAVE TO be in Column A or in the first column of the chart? Does all data have to be in one chart? On Separate worksheets? I'm sorry! Thank you! "CLR" wrote: Looks like a type-o..........replace the # with $ =Vlookup(A1,$D$1:$E$3,2,false) Vaya con Dios, Chuck, CABGx3 "TMF in MN" wrote in message ... bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
VLOOKUP requires at least 2 columns. Your latest formula has column P only.
=VLOOKUP(E6,$P$6:$P$152,3,FALSE) Change the table range to cover columns P and Q(and R if part of the table range) Not sure where you have the formula entered and which columns are the table range. =VLOOKUP(E6,$P$6:$R$152,3,FALSE) will look for a match to E6 in Column P then choose from column R which is column 3 in the table range of P6:R152 Enter the formula in a cell then copy down. Gord On Mon, 13 Jun 2005 15:59:01 -0700, TMF in MN wrote: I still can't get this to work. My reference cell is actually in column E and my 'table' to look up column E data is in column P,Q,R. Column P should match the data in column E and insert the value (name) entered in Column R. The reference chart (P,Q,R) is a separate table but on the same worksheet. The fomula I adapted from bj's response is: =VLOOKUP(E6,$P$6:$P$152,3,FALSE) I get a N/A result in Row 6 and #REF! when I copy the formula down. What am I doing wrong? Is the column Q messing me up? Does my reference data HAVE TO be in Column A or in the first column of the chart? Does all data have to be in one chart? On Separate worksheets? I'm sorry! Thank you! "CLR" wrote: Looks like a type-o..........replace the # with $ =Vlookup(A1,$D$1:$E$3,2,false) Vaya con Dios, Chuck, CABGx3 "TMF in MN" wrote in message ... bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
You must reference your *entire* data list,
P6 to R152, =VLOOKUP(E6,$P$6:$R$152,3,FALSE) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "TMF in MN" wrote in message ... I still can't get this to work. My reference cell is actually in column E and my 'table' to look up column E data is in column P,Q,R. Column P should match the data in column E and insert the value (name) entered in Column R. The reference chart (P,Q,R) is a separate table but on the same worksheet. The fomula I adapted from bj's response is: =VLOOKUP(E6,$P$6:$P$152,3,FALSE) I get a N/A result in Row 6 and #REF! when I copy the formula down. What am I doing wrong? Is the column Q messing me up? Does my reference data HAVE TO be in Column A or in the first column of the chart? Does all data have to be in one chart? On Separate worksheets? I'm sorry! Thank you! "CLR" wrote: Looks like a type-o..........replace the # with $ =Vlookup(A1,$D$1:$E$3,2,false) Vaya con Dios, Chuck, CABGx3 "TMF in MN" wrote in message ... bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
Actually Gord, although I'd prefer MATCH, which would give a location, you
could use it with one column, if say, you were checking to see if something did exist in a column: =VLOOKUP(B1,A:A,1,0) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... VLOOKUP requires at least 2 columns. Your latest formula has column P only. =VLOOKUP(E6,$P$6:$P$152,3,FALSE) Change the table range to cover columns P and Q(and R if part of the table range) Not sure where you have the formula entered and which columns are the table range. =VLOOKUP(E6,$P$6:$R$152,3,FALSE) will look for a match to E6 in Column P then choose from column R which is column 3 in the table range of P6:R152 Enter the formula in a cell then copy down. Gord On Mon, 13 Jun 2005 15:59:01 -0700, TMF in MN wrote: I still can't get this to work. My reference cell is actually in column E and my 'table' to look up column E data is in column P,Q,R. Column P should match the data in column E and insert the value (name) entered in Column R. The reference chart (P,Q,R) is a separate table but on the same worksheet. The fomula I adapted from bj's response is: =VLOOKUP(E6,$P$6:$P$152,3,FALSE) I get a N/A result in Row 6 and #REF! when I copy the formula down. What am I doing wrong? Is the column Q messing me up? Does my reference data HAVE TO be in Column A or in the first column of the chart? Does all data have to be in one chart? On Separate worksheets? I'm sorry! Thank you! "CLR" wrote: Looks like a type-o..........replace the # with $ =Vlookup(A1,$D$1:$E$3,2,false) Vaya con Dios, Chuck, CABGx3 "TMF in MN" wrote in message ... bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
Thanks RD.
I thought about that "at least 2 columns" statement after I posted and did not correct it. Pure sloth. Gord On Mon, 13 Jun 2005 16:49:46 -0700, "RagDyer" wrote: Actually Gord, although I'd prefer MATCH, which would give a location, you could use it with one column, if say, you were checking to see if something did exist in a column: =VLOOKUP(B1,A:A,1,0) |
THAT DID IT!!!
THANK YOU! "Gord Dibben" wrote: VLOOKUP requires at least 2 columns. Your latest formula has column P only. =VLOOKUP(E6,$P$6:$P$152,3,FALSE) Change the table range to cover columns P and Q(and R if part of the table range) Not sure where you have the formula entered and which columns are the table range. =VLOOKUP(E6,$P$6:$R$152,3,FALSE) will look for a match to E6 in Column P then choose from column R which is column 3 in the table range of P6:R152 Enter the formula in a cell then copy down. Gord On Mon, 13 Jun 2005 15:59:01 -0700, TMF in MN wrote: I still can't get this to work. My reference cell is actually in column E and my 'table' to look up column E data is in column P,Q,R. Column P should match the data in column E and insert the value (name) entered in Column R. The reference chart (P,Q,R) is a separate table but on the same worksheet. The fomula I adapted from bj's response is: =VLOOKUP(E6,$P$6:$P$152,3,FALSE) I get a N/A result in Row 6 and #REF! when I copy the formula down. What am I doing wrong? Is the column Q messing me up? Does my reference data HAVE TO be in Column A or in the first column of the chart? Does all data have to be in one chart? On Separate worksheets? I'm sorry! Thank you! "CLR" wrote: Looks like a type-o..........replace the # with $ =Vlookup(A1,$D$1:$E$3,2,false) Vaya con Dios, Chuck, CABGx3 "TMF in MN" wrote in message ... bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
AWWRIGHT!!!
Gord On Mon, 13 Jun 2005 17:15:02 -0700, TMF in MN wrote: THAT DID IT!!! THANK YOU! "Gord Dibben" wrote: VLOOKUP requires at least 2 columns. Your latest formula has column P only. =VLOOKUP(E6,$P$6:$P$152,3,FALSE) Change the table range to cover columns P and Q(and R if part of the table range) Not sure where you have the formula entered and which columns are the table range. =VLOOKUP(E6,$P$6:$R$152,3,FALSE) will look for a match to E6 in Column P then choose from column R which is column 3 in the table range of P6:R152 Enter the formula in a cell then copy down. Gord On Mon, 13 Jun 2005 15:59:01 -0700, TMF in MN wrote: I still can't get this to work. My reference cell is actually in column E and my 'table' to look up column E data is in column P,Q,R. Column P should match the data in column E and insert the value (name) entered in Column R. The reference chart (P,Q,R) is a separate table but on the same worksheet. The fomula I adapted from bj's response is: =VLOOKUP(E6,$P$6:$P$152,3,FALSE) I get a N/A result in Row 6 and #REF! when I copy the formula down. What am I doing wrong? Is the column Q messing me up? Does my reference data HAVE TO be in Column A or in the first column of the chart? Does all data have to be in one chart? On Separate worksheets? I'm sorry! Thank you! "CLR" wrote: Looks like a type-o..........replace the # with $ =Vlookup(A1,$D$1:$E$3,2,false) Vaya con Dios, Chuck, CABGx3 "TMF in MN" wrote in message ... bj, Thank you for your fast response. I got an error message, am I missing a set of parenthesees or something? THANK YOU! "bj" wrote: try in B1 =Vlookup(A1,$D$1:#E$3,2,false) copy down to B3 "TMF in MN" wrote: A B C D E 1 1 x 1 Oranges 2 1 x 2 Apples 3 2 x 3 Grapes If A1 matches any cell in D1 - D3, enter value in the matching E column into B1. Something like ... if A1= D1:D3, then E1:E3.. but I can't get it to work. THANK YOU SO MUCH!! TF |
| All times are GMT +1. The time now is 03:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com