If/Then Formula Help Needed

[SHARON]

I am trying to create a formula....

In worksheet 2 I have a listing with 3 columns labeled "area" "job #" and
"name" in columns a, b, and c respectively with over 100 rows.

In worksheet 1 I have a form where I want to be able to type the "job #" in
a cell G4, and i want it to automatically fill in the "name" in cell d3, and
to automattically fill in the "area" in cell k3.

I believe this is an if/then statement, however i can't get it to work. Any
help is greatly appreciated.

 

[Pete_UK]

Put this in D3:

=IF(G4="","",INDEX(Sheet2!C1:C100,MATCH(G4,Sheet2!B1:B100,0)))

and this in K3:

=IF(G4="","",INDEX(Sheet2!A1:A100,MATCH(G4,Sheet2!B1:B100,0)))

If you put a job# in G4 that is not in B1:B100 of the other sheet then
you will get a #N/A error.

Hope this helps.

Pete

 

[Fred Smith]

Telling us "i can't get it to work" doesn't give us much to go on. You'll
get much better help if you show us what formula you tried.

In this case, I suspect you want a Vlookup statement. Something like:

d3: =vlookup(g4,sheet2!A:C,2,false)
k3: =vlookup(g4,sheet2!A:C,3,false)

Rearrange your columns on worksheet 2 to have job # first, then area, then
name.

Regards,
Fred.

 

[Terry]

Try using a vlookup function here I think. In worksheet 2, if you re-arrange
your columns to to "job#", then "area" then "name", thats easiest.
Alternatively just insert a column in front of all 3 with a copy of the "job#"

Then in cell D3, where you want to return "name", your function will look
like this:

=VLOOKUP(G4,$A:$C,3,0) where G4 is where you have typed the job#,
$A:$C is the range of all the data and 3 looks in the 3rd column which should
be "name" if you just rearranged your columns (4 if you added a job# column
in front).

Then in k3, almost exactly the same formula, but change the 3 to which ever
colum you now want (will be either 2 or 3).

You may need to use absolute referencing ($) for which cell you are
referencing.

Hope this makes sense