Linking to value with a dynamic cell location?

[binar]

Fellow Forum Members,
I have a value that can appear anywhere on Sheet1, column A. In other words, it has no static cell position in Column A. It's location could be A1, A5, A19, etc... This value always has the following format: xxxxxxxxAAA (21 text characters always followed by AAA)

The problem is I need to link to this value from Sheet3 and since it's not a static cell location I have to manually setup the link myself.

Is there an Excel Function that can search through Sheet1, Column A for the text "AAA" and then return back a cell location of where it's at in Sheet1, Column A and then setup a link on Sheet3, A1? Such a function will eliminate the need for me to manually search through Sheet1 Column A and then manually link to it everytime the link needs to be reconnected. Does such a function involve using an Array? Any help will be greatly appreciated. Thanks.

 

[binar]

Try this...

=INDEX(Sheet1!A:A,MATCH("*AAA*",Sheet1!A:A,0))

T. Valko,
Thanks a thousand times for your help! I am totally amazed that only adding a little asterisk on the opposite end makes this formula much more powerful and elegant. It works perfectly. Even when I do a fill down. It's able to target what ever special characters I place inside the "*AAA*". Totally amazed ! Thanks.

 

[T. Valko]

T. Valko,
Thanks a thousand times for your help! I am totally amazed that only adding a little asterisk on the opposite end makes this formula much more powerful and elegant. It works perfectly. Even when I do a fill down. It's able to target what ever special characters I place inside the "*AAA*". Totally amazed ! Thanks.

You're welcome!

*AAA*

The asterisks are wildcards that mean:

Any single character or group of characters or no characters at all followed by AAA followed by any single character or group of characters or no characters at all.

 

[binar]

You're welcome!

*AAA*

The asterisks are wildcards that mean:

Any single character or group of characters or no characters at all followed by AAA followed by any single character or group of characters or no characters at all.

T. Valko,
Thanks for the explanation. It makes a lot of sense.

I hope you don't mind my revisiting the code below once again:

=INDEX(Sheet1!A:A,MATCH("*AAA*",Sheet1!A:A,0))


Is it even possible to modify this code to work within an IF logic structure.

For example. Can it be modified so that it is able to match for three different text strings at the same time which are for simplicity sake:

"*AAA*"
"*BBB*"
"*CCC*"


In other words, it still looks within Sheet1 Column A:A but this time it's able to look for three conditions at the same time and if a MATCH situation occurs it returns a value located on Cell B1, Sheet1. Any help will be greatly appreciated. Thanks in advance if you or any one out there want to take a stab at this modification.

 

[T. Valko]

T. Valko,
Thanks for the explanation. It makes a lot of sense.

I hope you don't mind my revisiting the code below once again:

=INDEX(Sheet1!A:A,MATCH("*AAA*",Sheet1!A:A,0))


Is it even possible to modify this code to work within an IF logic structure.

For example. Can it be modified so that it is able to match for three different text strings at the same time which are for simplicity sake:

"*AAA*"
"*BBB*"
"*CCC*"


In other words, it still looks within Sheet1 Column A:A but this time it's able to look for three conditions at the same time and if a MATCH situation occurs it returns a value located on Cell B1, Sheet1. Any help will be greatly appreciated. Thanks in advance if you or any one out there want to take a stab at this modification.

OK, do you mean you want to perform a lookup for the substring AAA or BBB or CCC?

In other words, look for any one of these three possible substrings?

 

[binar]

OK, do you mean you want to perform a lookup for the substring AAA or BBB or CCC?

In other words, look for any one of these three possible substrings?

T. Valko,
Thanks for your post. What I mean is match for "AAA" and "BBB" and "CCC" all within Column A:A, if match exists, return back to me the number "1" located inside Cell B1. I do not mean or, but instead three separate unique text strings.


I tested the function below and I can not get it to work when I do an auto fill down. It shows erroneous "NO MATCH" and "1" results in column C:C where the formula is located. I would appreciate it if you can try it out and see if you get the formula to work.


=IF(OR(ISERROR(VLOOKUP("*AAA*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*BBB*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*CCC*",$A2,1,FALSE))=FALSE)=TRUE,$B$1,"No Match")


The way I need it to work is like this:

Let's say cells A1 through A10 is populated with data as shown below:

A1 = blank
A2 = AAA
A3 = blank
A4 = blank
A5 = CCC
A6 = CCC
A7 = BBB
A8 = AAA
A9 = blank
A10=CCC


CELL B1 = 1


Column C:C needs to show the following results once I do an auto fill down from Cell C1 (where the formula is located) to Cell C10


C1 = no match
C2 = 1
C3 = no match
C4 = no match
C5 = 1
C6 = 1
C7 = 1
C8 = 1
C9 = no match
C10=1

All this data is dummy data to keep it simple. However, if it works with this dummy data, it should work with my real data. If this formula can work as explained above it would be the final revision I need to post in this thread.

Any help any one can provide will be greatly appreciated. Thanks.

 

[Aladin Akyurek]

...

The way I need it to work is like this:

Let's say cells A1 through A10 is populated with data as shown below:

A1 = blank
A2 = AAA
A3 = blank
A4 = blank
A5 = CCC
A6 = CCC
A7 = BBB
A8 = AAA
A9 = blank
A10=CCC


CELL B1 = 1


Column C:C needs to show the following results once I do an auto fill down from Cell C1 (where the formula is located) to Cell C10


C1 = no match
C2 = 1
C3 = no match
C4 = no match
C5 = 1
C6 = 1
C7 = 1
C8 = 1
C9 = no match
C10=1

All this data is dummy data to keep it simple. However, if it works with this dummy data, it should work with my real data. If this formula can work as explained above it would be the final revision I need to post in this thread.

Any help any one can provide will be greatly appreciated. Thanks.

The sample you posted and the lookup values of "AAA", "BBB", "CCC" taken
together do not impose using wild char...

In C1 enter and copy down:

=IF(ISNUMBER(MATCH(A1,{"AAA","BBB","CCC"},0)),$B$1,"no match")

If a look up value like AAA could exist in column A as part of string and that would be considered as a match...

In C1 enter one of the following and copy down:

=IF(SUM(COUNTIF(A1,"*"&{"AAA","BBB","CCC"}&"*")),$B$1,"no match")

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"AAA","BBB","CCC"},A1))),$B$1,"no match")

 

[T. Valko]

T. Valko,
Thanks for your post. What I mean is match for "AAA" and "BBB" and "CCC" all within Column A:A, if match exists, return back to me the number "1" located inside Cell B1. I do not mean or, but instead three separate unique text strings.


I tested the function below and I can not get it to work when I do an auto fill down. It shows erroneous "NO MATCH" and "1" results in column C:C where the formula is located. I would appreciate it if you can try it out and see if you get the formula to work.


=IF(OR(ISERROR(VLOOKUP("*AAA*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*BBB*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*CCC*",$A2,1,FALSE))=FALSE)=TRUE,$B$1,"No Match")


The way I need it to work is like this:

Let's say cells A1 through A10 is populated with data as shown below:

A1 = blank
A2 = AAA
A3 = blank
A4 = blank
A5 = CCC
A6 = CCC
A7 = BBB
A8 = AAA
A9 = blank
A10=CCC


CELL B1 = 1


Column C:C needs to show the following results once I do an auto fill down from Cell C1 (where the formula is located) to Cell C10


C1 = no match
C2 = 1
C3 = no match
C4 = no match
C5 = 1
C6 = 1
C7 = 1
C8 = 1
C9 = no match
C10=1

All this data is dummy data to keep it simple. However, if it works with this dummy data, it should work with my real data. If this formula can work as explained above it would be the final revision I need to post in this thread.

Any help any one can provide will be greatly appreciated. Thanks.

Still not sure I understand what you're wanting to do with this.

Try this entered in C1 then copied down as needed:

=IF(OR(A1={"AAA","BBB","CCC"}),1,"No Match")

 

[binar]

T. Valko,
Thanks for the formula you modified. After I made some minor changes it works perfectly. I am amazed how much less complicated it is compared to the IF formula I posted. Thanks again.

 

[T. Valko]

T. Valko,
Thanks for the formula you modified. After I made some minor changes it works perfectly. I am amazed how much less complicated it is compared to the IF formula I posted. Thanks again.

Good deal. Thanks for the feedback!