Excel/Excel Match/Hyperlink Function returning incorrect value
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.
Match((A2*B2),D:D) is equivalent to
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.
---------- 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.
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 firstname.lastname@example.org