Google-sheets – How to use HYPERLINK text as the first argument for MATCH

google sheetslinks

I have one column with hyperlinks and another column in which I do a vertical lookup using INDEX + MATCH. The catch is that the first argument for the MATCH function should be the correspondent value on the first column.

The problem is that it doesn't work because of the hyperlinks. If I remove them and leave the values as plain texts, it works.

I did some research and found that I can use the following formula to get the text value from a hyperlink cell:

=””&

Which works indeed if used like that, but not when it's the first argument of MATCH:

=INDEX(GBP_RATES, MATCH(""&A8, DATE_RATES, 0))

I get the following error:

#NAME?

Is there any workaround other than decoupling the text from the ULR into two different columns?

Best Answer

In your spreadsheet the issue is that the hyperlink as well as your named range DATE_RATES are dates.
In this case you do not need the & but the DATEVALUE function.

Please use the following formula:

=INDEX(GBP_RATES, MATCH(DATEVALUE(A1), DATE_RATES, 0))

How to use HYPERLINK date in MATCH?

Functions used:

Related Topic