VLOOKUP wonkiness

[rogero]

So a friend asked for help with VLOOKUP. I told him what to write. and it
doesn't work. But I think it should.

We have column A full of values (hundreds of work ids) and a list in column
C of the jobs my buddy has (his 20 or sounique work ids). He wants to use
the second column to quickly lookup up those in teh first that are his.

I said to use in Column B the formula =VLOOKUP(A1,$C$1:$C$21,1) to put data
in beside each job that's his. I had him put a '-' in C21 to fill in all
the non matches.
When he said it didn't work, I made a mockup that worked. So I had him send
over the sheet, and it doesn't work. I get a #N/A, and the details say
Value Not Available Error. It seems to gack at the lookup of the first
value.

It's of the format 0####-######. If I delete the leading 0 it works.
Huh? I'm not sure what's going on. But it defeats the purpose to have to
delete that leading 0 on several hundred entries first.

Any help? Using Win2000, Office2000.

thanks a bunch,

r

 

[Jeff]

You might try this:
=IF(ISERROR(VLOOKUP(A1,$C$1:$C$21,1)),"-",(VLOOKUP
(A1,$C$1:$C$21,1)))

(no wrapping)

 

[Guest]

Try changing the ID's format to General using the text to
columns option under data menu option. Just Highlight the
column and select data,text to columns, then click
finish. This will convert the id's to numeric format and
eliminate leading zero's.

 

[Jim]

You can easily use Data>Filter>Autofilter to return the data you want. If
you do not want to do it the easy way, then...

From my admittedly dim understanding of the problem, it seems you are not
using the correct function. The value you are looking up must be in the left
column of a multi-column table in order for the function to...well,
function. VLOOKUP looks in a multi-column table to find a value (such as
his/her name), then looks in the designated column of the table to find a
value. VLOOKUP will not return multiple instances, such as the Job Number
for each job that Joe (or Josephine) work on. Let's say you have a named
range ,Data in three EQUAL column ranges named Names , Job Numbers, and
HoursOnJob. To find the number of hours your friend worked on the jobs, use:

=SUMPRODUCT((Names="Joe")*(HoursOnJob))

To find how many jobs (s)he worked on:

=SUMPRODUCT((Names="Joe")*(JobNumbers))

I recommend the Autofilter method.

 

[Dave Peterson]

A couple basic questions...

You wrote your formula looked like:

=VLOOKUP(A1,$C$1:$C$21,1)

Since you're trying to match on work id's, I'd bet you'd want an exact match. I
think you'd want something like:

=VLOOKUP(A1,$C$1:$C$21,1,false)
(false in the 4th position means exact match)

And this is actually just retrieving the value that you gave it (returning
column 1 that you matched on).

Maybe you meant you wanted to return the second column over:
=VLOOKUP(A1,$C$1:$D$21,2,false)

And you may find it easier to move that table to a different worksheet. Then
you don't have to worry about inserting/deleting rows or columns.

=VLOOKUP(A1,sheet2!$a$1:$b$21,2,false)

And if you want to hide those errors when the keys don't match:

=if(iserror(VLOOKUP(A1,sheet2!$a$1:$b$21,2,false),"Missing",
VLOOKUP(A1,sheet2!$a$1:$b$21,2,false))
(all one cell)

=========
One of the biggest problems with =vlookup()'s are when the data that should be
matched is not the same.

If the value is 123, but in the lookup table, it's '123 (text), then you won't
get a match.

And you can't just change the format of the cells from text to number (or from
number to text). <Changing the format will work if you change the value of the
cell after--even just F2|Enter.>

You write that the format is like "0####-######". Does this mean that the value
is really a text string or that the value is numeric, but has a number format
like this?

And you'll have to answer that for both--the lookup value and the first column
of the lookup array.

I think I'd change all the entries to numbers and then life would be simpler:

Select column A and
edit|replace
- (dash)
with
(leave blank)
Replace all.

Then you can give the column a custom format of 0####-######.

Do the same for the first column in the lookup array.

Now your =vlookup() will be matching on the value of the cells (not how it's
shown in the cell with the formatting applied).

=====
Another option if the lookup value is numeric, but the first column in the
lookup array is text ('01234-123456).

=vlookup(text(a1,"0####-######"),sheet2!$a$1:$b$21,2,false)