V Lookup and Paste Special

[Manny]

I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data
in Worksheet B was paste special: values. I did subtotals on the original
data and wanted to see the subtotals without the detail this is why I pasted
special as values. .

Even when I change the format in Worksheet B to match in Worksheet A, it
doesnt work. The data in both worksheets are in Text but the VLookup still
doesnt work.

When I retype the values(names) in the first column of the data range, it
works. This may be fine for a few names but not a couple of hundred.

Does anyone know how to resolve this?

 

[ryguy7272]

I'm not sure about the values(names) thing, but vlookup is feeble under
certain circumstances. Try Index/Match as an alternative:
http://www.contextures.com/xlFunctions03.html#IndexMatch

That link should give you a few ideas and help you overcome the challenge
you face.

Regards,
Ryan--

 

[Pete_UK]

You can obtain your subtotals in a separate sheet by means of the
SUMIF function. Assume your names are in column A of both sheets, and
in SheetA you have the names once only (unique list) but there may be
several of each in SheetB. Assume you have some numbers in column B of
SheetB which you want sub-totalled in SheetA. Put this formula in B1
of SheetA:

=SUMIF(SheetB!A:A,A1,SheetB!B:B)

and copy down. The names in SheetB do not have to be in any particular
order.

Hope this helps.

Pete

 

[Manny]

Pete,

The issue isn't trying to reference the subtotals of names in two seperate
worksheets. What is the issue is trying to reference a name in one sheet to
another sheet.

Earlier I mentioned that the first column (which is a list of names) of the
Range_Lookup was pasted special as values. When I retype these names, the
function works, however, I am attempting to identify the solution so I wont
have to retype hundreds of names. So this means the function itself is
correct its just that the way the data in the cells are contained return a
#N/A error.

 

[Pete_UK]

Well, that indicates that you do not have an exact match - maybe you have
names in your table with spaces before or after them (which are difficult to
see, but which you do not type when you enter the name directly). You can
apply the TRIM function to your names in a helper column to remove spurious
spaces, then fix the values and then paste them back over the original names
to cure this problem.

If the names came from an HTML source (e.g. web-site) then you might have
some non-breaking spaces in there (character code 160), which TRIM will not
remove - you can use Find/Replace to get rid of those.

Hope this helps.

Pete

 

[Manny]

Thanks Pete, the TRIM function worked.

Well, that indicates that you do not have an exact match - maybe you have
names in your table with spaces before or after them (which are difficult to
see, but which you do not type when you enter the name directly). You can
apply the TRIM function to your names in a helper column to remove spurious
spaces, then fix the values and then paste them back over the original names
to cure this problem.

If the names came from an HTML source (e.g. web-site) then you might have
some non-breaking spaces in there (character code 160), which TRIM will not
remove - you can use Find/Replace to get rid of those.

Hope this helps.

Pete

 

[Pete_UK]

Glad to hear it, Manny - thanks for feeding back.

Pete