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
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 ????:
If any value in Column A of Sheet2 is an exact match to B2 in Sheet1AND any value in Column B of Sheet2exact 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 :
IFERROR
value = the argument that is checked for an error (from HYPERLINK up to MATCH formula)
value_if_error = "Hyperlink Error"
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")
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)
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
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)
发布评论
评论（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...
Basically translates to...
To break it down...
Formula Syntax :
Formula Breakdown :
--> 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.
The End Result...