test for value and return position

[Buster]

I am trying to accomplish two things.
1) I want to test for the existance of a value in a range of number that
varies from day to day.

2) I want to return the cell reference of the data obtained in question (1)

Any IDeas? I though of using something akin to H2:INDEX(H:H,COUNT(H:H)+1))
but I think I'm not on target here

Jeff

 

[Ron Rosenfeld]

I am trying to accomplish two things.
1) I want to test for the existance of a value in a range of number that
varies from day to day.

2) I want to return the cell reference of the data obtained in question (1)

Any IDeas? I though of using something akin to H2:INDEX(H:H,COUNT(H:H)+1))
but I think I'm not on target here

Jeff

=MATCH(val,H:H,FALSE) will return #N/A if the val does NOT exist.

=ADDRESS(MATCH(val,H:H,FALSE),8) will return the cell reference.

Use something like:

=IF(ISNA(MATCH(val,H:H,FALSE)),"value does not exist", "value exists")

and

=IF(COUNTIF(H:H,val)=0,"",ADDRESS(MATCH(val,H:H,FALSE),8))

Note that only the first MATCH will be returned if there are duplicates.


--ron

 

[Max]

Gave you the response below in your earlier multi-post*
in .worksheet.functions

*something to refrain from
--
Perhaps this example might help move you along a little?

Assume you have
in Sheet1, in A1:A5
---------------------------
11
22
33
44
55

In Sheet2
-------------
Assume you want to sum Sheet1 col A's numbers
from cell A1 until a cell in col A which number
matches a number input in A1 (in Sheet2)

Put in A1, say: 33

This number 33 matches the number in cell A3 in Sheet1
and what you want is: =SUM(Sheet1!A1:A3)
(i.e. 11+22+33 = 66)

If the number in A1 is 44,
what you want is: =SUM(Sheet1!A1:A4)
(i.e. 11+22+33+44 = 110)

And so on

Should there be no matching number in Sheet1's col A,
if Sheet2's A1 contains say: 35 (not found in Sheet1's col A),
let's assume we want blanks: "" to be returned

Try this to get the equivalent of the above ..

Put in B1:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",SUM(INDIRECT("'Sheet1'!A1:A"&MATCH(A1,Sh
eet1!A:A,0))))

 

[jeff.potts]

I've tried your suggestions and I'm not having much luck. When I tried
=IF(ISNA(MATCH(val,H:H,FALSE)),"value does not exist", "value exists")
I get Value Exists no matter what I have in the column. I tried
clearing all data and formulas, but the anomoly still exists.

When I tried the
=IF(COUNTIF(H:H,val)=0,"",ADDRESS(MATCH(val,H:H,FALSE),8)) calculation
I get a blank result even though the column i populated with at least
one value. To check things I did a Count(H:H) and get 1 so I should
therefore invoke the remaining portion of the If statemnet but it does
not

 

[Ron Rosenfeld]

I've tried your suggestions and I'm not having much luck. When I tried
=IF(ISNA(MATCH(val,H:H,FALSE)),"value does not exist", "value exists")
I get Value Exists no matter what I have in the column. I tried
clearing all data and formulas, but the anomoly still exists.

What did you substitute for val? If that is giving an error, you will get the
result you are seeing.

When I tried the
=IF(COUNTIF(H:H,val)=0,"",ADDRESS(MATCH(val,H:H,FALSE),8)) calculation
I get a blank result even though the column i populated with at least
one value. To check things I did a Count(H:H) and get 1 so I should
therefore invoke the remaining portion of the If statemnet but it does
not

I did a Count(H:H) and get 1

That means there is only one numeric value in column H but says nothing
about what it is.

I suspect that whatever value you are substituting for "val" and the values in
column H are not of the same type. Especially in view of your COUNT function
results, it is likely that the values in column H are really TEXT, and whatever
you are substituting for "val" in the formulas is NUMERIC.

If that is the case, try substituting a text string for "val": e.g. "1"
instead of 1.

If these pointers don't help, post an example of your actual data.






--ron

 

[jeff.potts]

The problem is the Val replacement issue. The colum to be checked is a
date formatted column upon import. How do I manage my "Val" to pick up
the dates. I get #NA when I use a date as my val replacement.

Do I need to convert it into excel serial date format and check for
existance and then convert back?

 

[Ron Rosenfeld]

The problem is the Val replacement issue. The colum to be checked is a
date formatted column upon import. How do I manage my "Val" to pick up
the dates. I get #NA when I use a date as my val replacement.

Do I need to convert it into excel serial date format and check for
existance and then convert back?

You have to make them the same, somehow.

From what you've written, especially the results of the COUNT formula, I
suspect that the dates that you have imported are not an Excel serial date but
rather are in text format (except perhaps for one of them, since COUNT(H:H)
gave you a 1 as a result).

You can prove or disprove that thesis by entering an ISNUMBER(H5) function
(assuming one of these dates is in H5). If it is an Excel date, you should get
a TRUE result. If you get a FALSE, it is likely a text string that looks like
a date. And, if you are importing it from a web page, there may be a <hard
non-breaking space> at the end (CHAR(160)).

Whether it will be easier to convert the dates in Column H to real dates, or
convert Val to an identical text string, is a decision that will await further
information from you about this data.


--ron