I'm having a little trouble with a lookup function I'm trying to write.
Hoping someone could set me straight on what I'm doing wrong:

on one tab of a worksheet I have a column (B) into which I type a "job
number". I want column C to look at Col B and return the "job name" for that
particular "job number". My next tab in the worksheet has those job names
and numbers -- which are laid out in the same columns as the previous tab
sheet. Currrently, this is what my forumula (in col. C of the first tab
sheet) looks like:

=IF(B321=0," ",VLOOKUP($B:$B,ListOfJobs!$B$4:$C$2000,2))

The first IF statement is so that it returns a blank space if nothing is
entered. The rest of it is to do as I commented.... look up the name of the
job that corresponds to the job number I just typed in, in the cell to the
left. I used the absolute range values since I was copying this down a range
of areas. Also.... at one point I thought I had it working right but then I
range-sorted the job number/name list on the index sheet and now nothing is
working right.

thoughts? Hints? ideas?

All comment gratefully received. Thanks!
--
Steven Leuck
Builders Electric, Inc.

=IF(B321=0," ",VLOOKUP(B321,ListOfJobs!$B$4:$C$2000,2,FALSE ))
I changed the lookup value to be a single value rather than the entire list,
and added the final argument of the vlookup function to require an exact
match.

"Steven Leuck" wrote:

I'm having a little trouble with a lookup function I'm trying to write.
Hoping someone could set me straight on what I'm doing wrong:

on one tab of a worksheet I have a column (B) into which I type a "job
number". I want column C to look at Col B and return the "job name" for that
particular "job number". My next tab in the worksheet has those job names
and numbers -- which are laid out in the same columns as the previous tab
sheet. Currrently, this is what my forumula (in col. C of the first tab
sheet) looks like:

=IF(B321=0," ",VLOOKUP($B:$B,ListOfJobs!$B$4:$C$2000,2))

The first IF statement is so that it returns a blank space if nothing is
entered. The rest of it is to do as I commented.... look up the name of the
job that corresponds to the job number I just typed in, in the cell to the
left. I used the absolute range values since I was copying this down a range
of areas. Also.... at one point I thought I had it working right but then I
range-sorted the job number/name list on the index sheet and now nothing is
working right.

thoughts? Hints? ideas?

All comment gratefully received. Thanks!
--
Steven Leuck
Builders Electric, Inc.

Hi Steven,

=IF(B321=0," ",IF(ISNA(VLOOKUP(B321,$B$4:$C$2000,2,0)),"EXA CT MATCH NOT
FOUND",(VLOOKUP(B321,$B$4:$C$2000,2,0))))

regards
--
*****
birds of the same feather flock together..



"Steven Leuck" wrote:

I'm having a little trouble with a lookup function I'm trying to write.
Hoping someone could set me straight on what I'm doing wrong:

on one tab of a worksheet I have a column (B) into which I type a "job
number". I want column C to look at Col B and return the "job name" for that
particular "job number". My next tab in the worksheet has those job names
and numbers -- which are laid out in the same columns as the previous tab
sheet. Currrently, this is what my forumula (in col. C of the first tab
sheet) looks like:

=IF(B321=0," ",VLOOKUP($B:$B,ListOfJobs!$B$4:$C$2000,2))

The first IF statement is so that it returns a blank space if nothing is
entered. The rest of it is to do as I commented.... look up the name of the
job that corresponds to the job number I just typed in, in the cell to the
left. I used the absolute range values since I was copying this down a range
of areas. Also.... at one point I thought I had it working right but then I
range-sorted the job number/name list on the index sheet and now nothing is
working right.

thoughts? Hints? ideas?

All comment gratefully received. Thanks!
--
Steven Leuck
Builders Electric, Inc.

Thanks to both BPeltzer and Driller for responses. I've tried your fixes and
they both work. 'preciate it very much!
--
Steven Leuck
Builders Electric, Inc.


"bpeltzer" wrote:

=IF(B321=0," ",VLOOKUP(B321,ListOfJobs!$B$4:$C$2000,2,FALSE ))
I changed the lookup value to be a single value rather than the entire list,
and added the final argument of the vlookup function to require an exact
match.

"Steven Leuck" wrote:

I'm having a little trouble with a lookup function I'm trying to write.
Hoping someone could set me straight on what I'm doing wrong:

on one tab of a worksheet I have a column (B) into which I type a "job
number". I want column C to look at Col B and return the "job name" for that
particular "job number". My next tab in the worksheet has those job names
and numbers -- which are laid out in the same columns as the previous tab
sheet. Currrently, this is what my forumula (in col. C of the first tab
sheet) looks like:

=IF(B321=0," ",VLOOKUP($B:$B,ListOfJobs!$B$4:$C$2000,2))

The first IF statement is so that it returns a blank space if nothing is
entered. The rest of it is to do as I commented.... look up the name of the
job that corresponds to the job number I just typed in, in the cell to the
left. I used the absolute range values since I was copying this down a range
of areas. Also.... at one point I thought I had it working right but then I
range-sorted the job number/name list on the index sheet and now nothing is
working right.

thoughts? Hints? ideas?

All comment gratefully received. Thanks!
--
Steven Leuck
Builders Electric, Inc.

You have your solution, but you may want to return "" instead of " ".



Steven Leuck wrote:

I'm having a little trouble with a lookup function I'm trying to write.
Hoping someone could set me straight on what I'm doing wrong:

on one tab of a worksheet I have a column (B) into which I type a "job
number". I want column C to look at Col B and return the "job name" for that
particular "job number". My next tab in the worksheet has those job names
and numbers -- which are laid out in the same columns as the previous tab
sheet. Currrently, this is what my forumula (in col. C of the first tab
sheet) looks like:

=IF(B321=0," ",VLOOKUP($B:$B,ListOfJobs!$B$4:$C$2000,2))

The first IF statement is so that it returns a blank space if nothing is
entered. The rest of it is to do as I commented.... look up the name of the
job that corresponds to the job number I just typed in, in the cell to the
left. I used the absolute range values since I was copying this down a range
of areas. Also.... at one point I thought I had it working right but then I
range-sorted the job number/name list on the index sheet and now nothing is
working right.

thoughts? Hints? ideas?

All comment gratefully received. Thanks!
--
Steven Leuck
Builders Electric, Inc.

--

Dave Peterson