V Lookup EZ Tutorial anyone?

  • Thread starter Thread starter Jack Slater
  • Start date Start date
I have 1 spreadsheet with a list of part numbers and other data. I have a
2nd spreadsheet that has the list of part numbers and one element that I
need to bring (lookup?) to the 1st spreadsheet when the part number exists
in both. Clear?
Thanks.
Jack
 
Not working yet -
I have the 2 sheets in file now and pasted the formula in a blank column in
sheet 1 but it is returning the NA for a part that IS listed in the 2nd
sheet which does have a value in column 2?? 2nd sheet is sorted in
ascending order - is there something else I need to do with it?
Jack
 
If it returns NA you can be sure it is NOT there! It may be that you are
looking up text when it should be a numeric value (or vice-versa), or (if
it's text) that you have spaces that you can't see, or (if it's a numeric
value) that it isn't exactly equal.

As a test, try copying the cell from the lookup range and pasting it in
whichever cell is providing the value to be looked up (i.e. A1 for
=VLOOKUP(A1...)).

Jack Slater said:
Not working yet -
I have the 2 sheets in file now and pasted the formula in a blank column in
sheet 1 but it is returning the NA for a part that IS listed in the 2nd
sheet which does have a value in column 2?? 2nd sheet is sorted in
ascending order - is there something else I need to do with it?
Jack

Dave Peterson said:
The basic formula looks like:

=vlookup(a1,sheet2!$a$1:$z$9999,2,false)

Uses the value in A1 to search sheet2's A1:A9999. When it finds a
Click to expand...
match,
it
returns the 2 column (B) in that lookup range (A1:z9999).

But if it doesn't find a match, it returns #n/a's. You can hide them with:

=if(iserror(vlookup(...)),"Missing",vlookup(...))
(the vlookup formula is duplicated.)

And one more slight problem. If the cell to be returned is empty, then the
formula will show a 0.

You can stack one more level of checking to it:

=if(iserror(vlookup(...)),"Missing",if(vlookup(...)="","",vlookup(...)))

and if you want to prepopulate the formula in a bunch of cells, and want the
cell to look empty until you put something in A1.

=if(a1="","",oneofthoseextremelylongvlookupformulashere)

The good news is once you get one formula correct, you can copy it down your
range.
Click to expand...
have
Click to expand...
 
the font of the copied item form the lookup table is what pasted in the main
page so it did paste correctly.
 
I got a private email saying that Jack had solved the problem. He didn't
explain what it was, but I think he was happy.
 
Back
Top