You are here:

Excel/Excel Match/Hyperlink Function returning incorrect value

Advertisement


Question
QUESTION: Hi,
I am using a match function within a hyperlink function to find the value of 2 multiplied cells and jump to this value as listed in Column D. For example, I am entering one number in cell A2 and a second number in cell B2. The following formula is what I have entered in cell C2:
=HYPERLINK("#D"&MATCH((A2*B2),D:D), "Jump to" &(A2*B2))
This formula correctly multiplies cell A2 and B2 and I want to jump to the value of this multiplied number as listed in Column D. It works some of the time but other times just jumps to a completely randomn number in column D. The multiplication works and the hyperlink reads 'Jump to (correct multiplied number)' but it jumps to a different number in the column. I was wondering what I am doing wrong. Please let me know if more information is needed. Thanks so much for your help.

ANSWER: Hannah,

Match((A2*B2),D:D) is equivalent to

Match((A2*B2),D:D),1)

since a 3rd argument of 1 is the default value and if you enter no 3rd argument it defaults to 1.  The 1 means your data is sorted ascending. It sounds like that isn't the case for you.  

When the third argument is 1 or left blank, then the behavior of the match function is:

MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

so that is why you are seemingly jumping to random numbers.  The fix is to use a third argument of zero.  This finds an exact match and makes no assumption about how your data is arranged.

so just adjust you formula to this

=HYPERLINK("#D"&MATCH((A2*B2),D:D,0), "Jump to" &(A2*B2))

and you should be all set I would think.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: I have one follow-up question to this thread. Column D is the multiplied value of a number in Column E and Column F. Therefore, there are sometimes where the values are paired several times in separate columns (eg Column E has 17000 and Column F has 17111 in one row then in another row Column E has 17111 and Column F has 17000). I was wondering how the formula I am using (=HYPERLINK("#D"&MATCH((A2*B2),D:D,0), "Jump to" &(A2*B2))) can return all rows that contain the multiplied number in Column D.
Thank you again so much for your help.

Answer
Hannah,

A hyperlink can only point to one location.  If you wanted to select multiple cells and then say tab through the selections, then a macro would probably be the best approach.

If you wanted to build multiple hyperlinks, one to each cell, then that could be done with formulas.  It might require a scratch area where the target rows are collected or it might be something that could be done in place like the above formula with one hyperlink per occurance.  

This would require entering the hyperlink formula as an array formula and I don't know if that will work or not as I have never tried it.

If you want to send a sample file (it could contain dummy data) with your preferences/limitations, I can take a look at how to do it.  Send it to twogilvy@msn.com

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.