HYPERLINK, MATCH functions

[F. Lawrence Kulchar]

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!!!!!!!!!!!!!!

 

[T. Valko]

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&"*"),Sheet1!A1:A1077,Sheet1!E1:E1077),0)

C1:

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

Or, you could combine them both into one:

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

 

[FLKulchar]

thank you...works perfectly!

FLKulchar

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&"*"),Sheet1!A1:A1077,Sheet1!E1:E1077),0)

C1:

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

Or, you could combine them both into one:

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

 

[T. Valko]

You're welcome. Thanks for the feedback!