1

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?

3
  • I have tried it and works fine for match e.g. =MATCH(""&K16, DATE_RATES, 0) as well as =INDEX(GBP_RATES, MATCH(""&K16, DATE_RATES, 0)). Can you please share a sheet with the issue reproduced? – marikamitsos Apr 30 '20 at 17:14
  • Have you followed the pattern used at this answer? – marikamitsos Apr 30 '20 at 17:20
  • @marikamitsos you sure that K16 is a HYPERLINK? It still doesn't work on my end. Here's a sheet that reproduces the problem. – Paul Razvan Berg Apr 30 '20 at 18:36
1

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:

2
  • Fantastic, thanks so much. A quick final question: is there a way to abstract away this function? That is, create a function implementation that accepts two arguments (currency and date) and returns the exchange rate for those two values. – Paul Razvan Berg May 1 '20 at 11:03
  • "Fantastic, thanks so much. ...is there a way to ...create a function...returns the exchange rate...". Yes there is. Still. This is completely different and totally out of the scope of this question. As for this question. Glad I could help :) – marikamitsos May 1 '20 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.