Followup to "worksheet function"

[SteveDB1]

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,SUMMARY!$A$10:$A$60,0)))

And these are Pete'
=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)),"",VLOOKUP(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 are:
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.

 

[Pete_UK]

You will get #N/A with Roger's formula if there is no exact match, and
a blank cell if E5 is blank. With mine you will get a blank cell if
there is no match (I don't check for E5 being empty). Actually, your
range in the second formula goes to B82 instead of B60, but that
doesn.t really affect things.

So, what the results are telling you is that there is no exact match.

This implies that there is something different between the value in E5
and the values in column A. If these are text values that look the
same, then perhaps you have extra spaces in E5 or column A so there is
no exact match. If they are meant to be numeric values, then perhaps
one set of data is numeric and the other is actually text that happens
to look like it's numeric.

I would suggest that you examine your data more carefully. You can use
=LEN(cell) to find out how many characters are in the cell if they are
text strings. You can use =ISNUMBER(cell) to check if the cell
contains a number.

Hope this helps.

Pete

 

[SteveDB1]

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.

 

[Pete_UK]

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

 

[SteveDB1]

Hi again Pete. Thanks for getting back to me so quickly.
I think that I figured part of my problem out.
I found that if the data type on my summary/sum sheet is a text, and the
data type on my primary sheet is say, general, I have to change the data type
on one sheet to match the other. And then I need to activate each cell to
"force" the data type conversion.
We had this same exact issue two and a half years ago with sumproduct. The
solution that either Roger, or another guy who was helping provided was
either a double negative, or &"" in front of, or following the row/column
arrays.
Would those apply here as well? I only ask because I've found that with some
of my attempts at using those data-type nullifiers, they don't always resolve
the problem.
Again-- thank you.

 

[SteveDB1]

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

 

[Roger Govier]

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 Special>values 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))),"")

 

[SteveDB1]

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]

Hi Steve

It might help if I could spell Substitute correctly, twice in succession<bg>

If you used
=--SUBSTITUTE(SUBSTITUTE(A10," ",""),CHAR(160),"")
That would also turn any text numbers into numeric.

If the value being converted was true text, however, the -- would cause
#VALUE to be shown.

 

[Pete_UK]

Hi Steve,

(I'm back !!)

a slightly faster approach than Roger's formula is to use Find &
Replace to get rid of your spaces. Highlight column A of the SUMMARY
sheet by clicking on the column identifier. Then do CTRL-H as follows:

Find what: type a single space
Replace with: leave blank

Click Replace All

Hope this helps.

Pete

 

[SteveDB1]

So, based on both of your responses, you don't think that it's a data-type
matter?