Excel 2003 - Nested IF functions, VLOOKUP, or something else?
 

I have Sheet 1 that has the following data which is set and will never be
changed

place# place name distance
2000 xxx 10
2001 yyy 20
2002 zzz 30

I have Sheet 2 with a blank form to fill out

place# place name distance
2000


I want to be able to only have to put in the place# and have the
corresponding place name and distance to fill in automatically. I have
messed around with the nested IF functions and vlookup but now I am confused.
The problem I am getting stuck at is the logical test part of the IF
function. I can have the function (in the place name cell) check a single
cell in the place# column and return the corresponding place name but that's
a lot of forumlas.

I know this is brief but I was hoping I could get a general answer for now.

Excel 2003 - Nested IF functions, VLOOKUP, or something else?
 

Hi

In cell B2 of Sheet2 enter
=IF($A2="","",INDEX(Sheet1!$A$2:$C$4,
MATCH(Sheet2!$A2,Sheet1!$A$2:$A$4,0),COLUMN()))
Copy across to C2
Copy B2:C2 down page as far as required.

Change ranges to suit.
--
Regards
Roger Govier

ksell87 wrote:
I have Sheet 1 that has the following data which is set and will never be
changed

place# place name distance
2000 xxx 10
2001 yyy 20
2002 zzz 30

I have Sheet 2 with a blank form to fill out

place# place name distance
2000


I want to be able to only have to put in the place# and have the
corresponding place name and distance to fill in automatically. I have
messed around with the nested IF functions and vlookup but now I am confused.
The problem I am getting stuck at is the logical test part of the IF
function. I can have the function (in the place name cell) check a single
cell in the place# column and return the corresponding place name but that's
a lot of forumlas.

I know this is brief but I was hoping I could get a general answer for now.

Excel 2003 - Nested IF functions, VLOOKUP, or something else?
 

Thank you very much Roger!

"Roger Govier" wrote:

Hi

In cell B2 of Sheet2 enter
=IF($A2="","",INDEX(Sheet1!$A$2:$C$4,
MATCH(Sheet2!$A2,Sheet1!$A$2:$A$4,0),COLUMN()))
Copy across to C2
Copy B2:C2 down page as far as required.

Change ranges to suit.
--
Regards
Roger Govier

ksell87 wrote:
I have Sheet 1 that has the following data which is set and will never be
changed

place# place name distance
2000 xxx 10
2001 yyy 20
2002 zzz 30

I have Sheet 2 with a blank form to fill out

place# place name distance
2000


I want to be able to only have to put in the place# and have the
corresponding place name and distance to fill in automatically. I have
messed around with the nested IF functions and vlookup but now I am confused.
The problem I am getting stuck at is the logical test part of the IF
function. I can have the function (in the place name cell) check a single
cell in the place# column and return the corresponding place name but that's
a lot of forumlas.

I know this is brief but I was hoping I could get a general answer for now.
.