Hi Roger,
The data was not copied from the web.
One of the things I did find was, as stated, a data-type difference between
the two worksheets. I don't know if you remember-- enough time has passed--
but I'd posted back in October of 06 asking about troubles I was having with
Sumproduct. Harley had tried answering my posts, and we both ended up getting
short with each other. At that point you'd stepped in, and helped me work
through the details of my problem. Which wound up being a data-type
difference. I now use &"" in all my work with sumproduct because I've found
it to work the best.
This morning, after I'd reposted, I decided to look at my data-types and
found that my data on the Sum page was text in some cases, and in others it
was general. The data on my sheet where the equation was located was mostly
general, with an occasional number data-type.
As I found that, I decided to change the data-type by standard means, and
then go through and activate each cell, thus "forcing" the conversion. I then
checked my base sheet, and all of the data I wanted was there.
Hence, I'm now wondering if there's a data-type nullifier that'd resolve
this for future reference/usage.
I will look at your substitute routine to see if that'd resolve it, but it's
still a point that I need to resolve here.
Again, thanks for your helps...



"Roger Govier" wrote:

Hi Steve

only just picked up on this thread.
It sounds as though you may have leading or trailing spaces, or, if the data
has been copied from the web, then the non-breaking space Char(160).

You could build in to the formula, substituting these characters with Null,
but I would be more inclined to "clean" the source data.

In a spar column on the source data sheet
=SUBSTITUTE(SUSTITUTE(A10," ",""),Char(160),"")
copy down as far as required
Copy this "cleaned data" and paste Specialvalues back over the data in
column A.

Pete's formula does deal with case of the value not being matched (a good
idea) but it could perhaps be achieved slightly more efficiently by using a
single function call with Countif to check if E5 exists in column A.
The following tests for Null value in E5 and No Match being found.

=IF(E5="","",IF(COUNTIF(SUMMARY!$A$10:$A$60,E5),
INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SUMMARY!$A$10:$ A$60,0))),"")

--
Regards
Roger Govier

"SteveDB1" wrote in message
...
yea, it looks like we were posting within minutes of each other, and the
list
delay for the newsgroup was causing the overlaps.

I understood/stand that if there's no value, the cell would remain blank.
Which was indeed part of what I'd wanted. I also understood that Match
would
return an #N/A error if it did not fint an exact match. Which was why I'd
thought of using it, but "mis-ordered" my version in comparing my original
to
yours, and Roger's.
At this point, I'm thinking that it's a data-type issue, and am wondering
if
there is a data-type nullifier that'd resolve the issues. I am aware
of --,
and &"".
I have had occurrences that I've tried both of those and they didn't work
for my purposes-- and yes, I'd be willing to consider that there was
something else that I'd missed which invalidated their affect.
Again, thank you for your time.
Steve


"Pete_UK" wrote:

Steve,

our posts are crossing.

All 3 formulae are looking for exact matches, so the values have to
match exactly. I suggest you have different data types in the lookup
value and the lookup vector.

I'm going out soon, so there won't be any more clashes in posting
times.

Pete

On Feb 3, 4:16 pm, SteveDB1
wrote:
Ok, now I'm really confused. I just tried Roger's version on two more
files,
and it works fine.
Which makes me wonder if there are specific workbook attributes, or
properties that would limit the working of the nested functions.



"SteveDB1" wrote:
Morning again folks.
Yesterday I'd posted a question regarding looking up and comparing
data
between two worksheets, and placement of a marker in a tertiary
column. Roger
Govier, and Pete_UK graciously responded, and had provided an answer
to my
questions.
They provided the following nested functions, with my modifications
now
included.
The first is Roger's
=IF(E5="","",INDEX(SUMMARY!$B$10:$B$60,MATCH(E5,SU MMARY!$A$10:$A$60,0)))

And these are Pete's
=IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",INDEX (SUMMARY!$B$10:$B82,MATCHÂ*(E5,SUMMARY!$A$10:$A$60 ,0)))

=IF(ISNA(MATCH(E5,SUMMARY!$A$10:$A$60,0)),"",VLOOK UP(E5,SUMMARY!$A$10:$B$60Â*,2,0))

I've saved these in a notepad file so I have them at the ready for my
use.
At first, all three of these worked. All three provided the same
exact
answers when placed side by side.

Later on in the day-- yesterday afternoon, and again this morning, I
set
each of them in a different worksheet, modified the column, and row
values.
Roger's now leaves me with the #N/A error, and Pete's leave the
column blank
with no corresponding letters.
I've gone through and made sure that my row upper and lower limits
are
correct, that my columns, and other elements are all accurate--
double, and
triple checking myself-- and I keep getting the identical response--
#N/A, or
blanks (where letter values should be).

My questions a
1- the most obvious.... what's wrong.
2- are there certain conditions beyond the obvious (columns, rows)
that are
required for this configuration of nested functions to work? I.e.,
they all
worked in one file, and now don't work in the remainder of my files
(three
that I've tried since the first yesterday morning).
3-I just went back, used Roger's original equation, selected the
correct
columns, placed absolute references B$9:..... to make sure that when
I drag
it down the top, and bottom limits aren't skewed. I'm still receiving
the
#N/A error.

Your helps are appreciated.
Best.- Hide quoted text -

- Show quoted text -