Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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) |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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) |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
formula to combine cells using if/then | Excel Worksheet Functions |