V Lookup EZ Tutorial anyone?

[Jack Slater]

I need some help - any EZ to understand tutorials out there?

 

[Jack Slater]

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

 

[Jack Slater]

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

 

[Anon]

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...)).

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

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

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.

have

 

[Jack Slater]

the font of the copied item form the lookup table is what pasted in the main
page so it did paste correctly.

 

[Dave Peterson]

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.