HYPERLINK, MATCH functions

  • Thread starter F. Lawrence Kulchar
  • Start date

F

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

Advertisements

T

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")
 
F

FLKulchar

thank you...works perfectly!

FLKulchar
T. Valko said:
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")
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top