Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
TMF in MN
 
Posts: n/a
Default 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   Report Post  
bj
 
Posts: n/a
Default

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   Report Post  
TMF in MN
 
Posts: n/a
Default

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   Report Post  
CLR
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
TMF in MN
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
RagDyer
 
Posts: n/a
Default

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   Report Post  
RagDyer
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
TMF in MN
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
put formula results into a different cell if it is empty PutFormula Excel Worksheet Functions 2 February 11th 05 04:31 AM
how do i write a formula and keep in in formula form, so it DOESN. norcalchick2207 Excel Discussion (Misc queries) 2 February 4th 05 09:38 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
formula to combine cells using if/then Roland Excel Worksheet Functions 1 January 25th 05 03:51 PM


All times are GMT +1. The time now is 03:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"