Is there any way to create a hyperlink in excel with multiple conditionals?

Question

An extended question from the original post: Conditional Hyperlink if Value from Sheet1 Matches Value in Sheet2

Is there any way to hyperlink with multiple conditionals? Would I need to utilise IF / AND statements? 🤔

FOR EXAMPLE: When data in two different columns in Sheet 1, B2 ("Number") and C2 ("Letter"), MATCH two columns in Sheet 2, A2 ("Number) and Column B2 ("Letter"), then cell A2 ("Link") in Sheet 1 hyperlinks to A2 in Sheet 2

Answer 1

I was able to figure it out and wanted to post the logic should it prove to be helpful to anyone else. It's a bit lengthy but wanted to explain as much as possible to [hopefully] assist your understanding ?:

Formula Used...

=IFERROR(HYPERLINK("#"&CELL("address",INDEX(Sheet2!$A:$A,MATCH(1,(Sheet2!A:A=Sheet1!B2)*(Sheet2!B:B=Sheet1!C2),0))), "Link"),"Hyperlink Error")

Basically translates to...

If any value in Column A of Sheet2 is an exact match to B2 in Sheet1 AND any value in Column B of Sheet2 exact match to C2 in Sheet1, HYPERLINK to that relevant cell in Column A of Sheet2 (A5 in this case) and display "Link" in place of the formula; Otherwise (if error), display "Hyperlink Error"

To break it down...

Formula Syntax :

• IFERROR(value, value_if_error)
• HYPERLINK(link_location, [friendly_name])
• CELL(info_type, [reference])
• INDEX(array, row_num, [column_num])
• MATCH(lookup_value, lookup_array, [match_type])

Formula Breakdown :

1. IFERROR

• value = the argument that is checked for an error (from HYPERLINK up to MATCH formula)
• value_if_error = "Hyperlink Error"

2. HYPERLINK(link_location, [friendly_name])

• link_location = the path and file name to the document to be opened (from "#"&CELL up to MATCH formula)
• [friendly_name] = (optional) the text or numeric value that is displayed in the cell ("Link")

3. CELL(info_type, [reference])

• info_type = a text value that specifies what type of cell information you want to return ; various types available but the above formula uses "address" which is a reference of the first cell in reference, as text.
• [reference] = (optional) the cell you want information about (from INDEX up to MATCH formula)

4. INDEX(array, row_num, [column_num])

• array = a range of cells or an array constant; Column A of Sheet2

--> If array contains only one row or column, the corresponding row_num or column_num argument is optional.

--> If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.

• row_num = selects the row in array from which to return a value ; (our MATCH formula)
• [column_num] = (optional) selects the column in array from which to return a value ; Not used in our formula

5. MATCH(lookup_value, lookup_array, [match_type])

• lookup_value = the value that you want to match in lookup_array ; it can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value ; (the 1 used in our Formula = TRUE ; if FALSE = 0)
• lookup_array = the range of cells being searched ((Sheet2!A:A=Sheet1!B2)[asterisk](Sheet2!B:B=Sheet1!C2) where the [asterisk] in the middle denotes "AND", meaning both conditions must be met)
• [match_type] = (optional) specifies how Excel matches lookup_value with values in lookup_array where -1 = greater than, 0 = exact, or 1 = less than (our formula uses "0" to look for an Exact Match)

The End Result...

SHEET1

SHEET2