HYPERLINK, MATCH functions
 

A B
C
1 Walla Walla =MATCH(A1&"*",Sheet1!A1:A1077,0)

=HYPERLINK("#sheet1!A"&B1,"GO TO")


Obiouosly, my hyperlink in cell C1 sends me to ColumnA570, because cell B1
has 570 embossed in it...because Walla Walla appears in Sheet1, column A, row
570!

QUESTION: HOW CAN I ALSO SEARCH "Walla Walla" in Sheet 1 , column E as
well as in colum A (as is presently the case)?

I don't wish to use 2 MATCH and HYPERLINK functions -- but, I wish to
combine the 2 functions something like:

=MATCH(A1&"*", OR(Sheet1!A1:A1077,Sheet1!E1:E1077),0)

followed by;

=HYPERLINK(OR(("#sheet1!A"&B1,"#sheet1!E"&B1),"GO TO")

Thamks:

FLKulchar

PS: My method, just described above does NOT work!!!!!!!!!!!!!!

HYPERLINK, MATCH functions
 

I'm assuming that the lookup value *does* exist in one range or the other so
there's no error checking.

B1:

=MATCH(A1&"*",IF(COUNTIF(Sheet1!A1:A1077,A1&"*"),S heet1!A1:A1077,Sheet1!E1:E1077),0)

C1:

=HYPERLINK("#sheet1!"&IF(COUNTIF(Sheet1!A1:A1077,A 1&"*"),"A","E")&B1,"GO
TO")

Or, you could combine them both into one:

=HYPERLINK("#sheet1!"&IF(COUNTIF(Sheet1!A1:A1077,A 1&"*"),"A","E")&MATCH(A1&"*",IF(COUNTIF(Sheet1!A1: A1077,A1&"*"),Sheet1!A1:A1077,Sheet1!E1:E1077),0), "GO
TO")


--
Biff
Microsoft Excel MVP


"F. Lawrence Kulchar" wrote in
message ...

A B
C
1 Walla Walla =MATCH(A1&"*",Sheet1!A1:A1077,0)

=HYPERLINK("#sheet1!A"&B1,"GO TO")


Obiouosly, my hyperlink in cell C1 sends me to ColumnA570, because cell B1
has 570 embossed in it...because Walla Walla appears in Sheet1, column A,
row
570!

QUESTION: HOW CAN I ALSO SEARCH "Walla Walla" in Sheet 1 , column E as
well as in colum A (as is presently the case)?

I don't wish to use 2 MATCH and HYPERLINK functions -- but, I wish to
combine the 2 functions something like:

=MATCH(A1&"*", OR(Sheet1!A1:A1077,Sheet1!E1:E1077),0)

followed by;

=HYPERLINK(OR(("#sheet1!A"&B1,"#sheet1!E"&B1),"GO TO")

Thamks:

FLKulchar

PS: My method, just described above does NOT work!!!!!!!!!!!!!!

HYPERLINK, MATCH functions
 

thank you...works perfectly!

FLKulchar
"T. Valko" wrote in message
...
I'm assuming that the lookup value *does* exist in one range or the other
so there's no error checking.

B1:

=MATCH(A1&"*",IF(COUNTIF(Sheet1!A1:A1077,A1&"*"),S heet1!A1:A1077,Sheet1!E1:E1077),0)

C1:

=HYPERLINK("#sheet1!"&IF(COUNTIF(Sheet1!A1:A1077,A 1&"*"),"A","E")&B1,"GO
TO")

Or, you could combine them both into one:

=HYPERLINK("#sheet1!"&IF(COUNTIF(Sheet1!A1:A1077,A 1&"*"),"A","E")&MATCH(A1&"*",IF(COUNTIF(Sheet1!A1: A1077,A1&"*"),Sheet1!A1:A1077,Sheet1!E1:E1077),0), "GO
TO")


--
Biff
Microsoft Excel MVP


"F. Lawrence Kulchar" wrote
in message ...

A B
C
1 Walla Walla =MATCH(A1&"*",Sheet1!A1:A1077,0)

=HYPERLINK("#sheet1!A"&B1,"GO TO")


Obiouosly, my hyperlink in cell C1 sends me to ColumnA570, because cell
B1
has 570 embossed in it...because Walla Walla appears in Sheet1, column A,
row
570!

QUESTION: HOW CAN I ALSO SEARCH "Walla Walla" in Sheet 1 , column E as
well as in colum A (as is presently the case)?

I don't wish to use 2 MATCH and HYPERLINK functions -- but, I wish to
combine the 2 functions something like:

=MATCH(A1&"*", OR(Sheet1!A1:A1077,Sheet1!E1:E1077),0)

followed by;

=HYPERLINK(OR(("#sheet1!A"&B1,"#sheet1!E"&B1),"GO TO")

Thamks:

FLKulchar

PS: My method, just described above does NOT work!!!!!!!!!!!!!!

HYPERLINK, MATCH functions
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"FLKulchar" wrote in message
...
thank you...works perfectly!

FLKulchar
"T. Valko" wrote in message
...
I'm assuming that the lookup value *does* exist in one range or the other
so there's no error checking.

B1:

=MATCH(A1&"*",IF(COUNTIF(Sheet1!A1:A1077,A1&"*"),S heet1!A1:A1077,Sheet1!E1:E1077),0)

C1:

=HYPERLINK("#sheet1!"&IF(COUNTIF(Sheet1!A1:A1077,A 1&"*"),"A","E")&B1,"GO
TO")

Or, you could combine them both into one:

=HYPERLINK("#sheet1!"&IF(COUNTIF(Sheet1!A1:A1077,A 1&"*"),"A","E")&MATCH(A1&"*",IF(COUNTIF(Sheet1!A1: A1077,A1&"*"),Sheet1!A1:A1077,Sheet1!E1:E1077),0), "GO
TO")


--
Biff
Microsoft Excel MVP


"F. Lawrence Kulchar" wrote
in message ...

A B
C
1 Walla Walla =MATCH(A1&"*",Sheet1!A1:A1077,0)

=HYPERLINK("#sheet1!A"&B1,"GO TO")


Obiouosly, my hyperlink in cell C1 sends me to ColumnA570, because cell
B1
has 570 embossed in it...because Walla Walla appears in Sheet1, column
A, row
570!

QUESTION: HOW CAN I ALSO SEARCH "Walla Walla" in Sheet 1 , column E as
well as in colum A (as is presently the case)?

I don't wish to use 2 MATCH and HYPERLINK functions -- but, I wish to
combine the 2 functions something like:

=MATCH(A1&"*", OR(Sheet1!A1:A1077,Sheet1!E1:E1077),0)

followed by;

=HYPERLINK(OR(("#sheet1!A"&B1,"#sheet1!E"&B1),"GO TO")

Thamks:

FLKulchar

PS: My method, just described above does NOT work!!!!!!!!!!!!!!