Hash operators within Index/Match
Our take
Intermediate-Advanced user on (Office 365 - Windows 11). My workbook is mostly developed in Excel desktop, and the live co-authored workbook is hosted on SharePoint.
I have an issue running hash-operators within an INDEX/MATCH formula. The resulting value does not align with the formula evaluation conclusion.
As a hypothetical example about what's happening: Col A contains a spilled array listing unique identifiers, Col B and Col C contain spilled arrays of data, and Col D contains an Index/Match formula. The formula is rooted in Cell D2, and spills down equal with the other columns (preceding it with [ =IF(B2#="","", ] forces it to spill). The formula finds the value of Col A based on the always-unique permutations resulting from searching B2# and C2# together.
In this example:
D2 should find A6,
D3 should find A5, and
D4 should find A7.
However, all results mirror the first answer. I.e. D2, D3, and D4, all point to A6.
There are no formula errors, and the formula evaluator shows everything working properly. For example, evaluation of D3 (even though it is spilled) shows that it processes perfectly, beginning to end. D3 evaluation concludes by correctly identifying the A5 value. However, the output on the sheet shows the A6 value, directly conflicting with the formula evaluator.
Any insights would be greatly appreciated.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Index Match not automatically updating?This is the first time I've encountered this issue. I am using index match in one workbook to search the values/data in another workbook. I noticed that if I update the source workbook, the other workbook doesn't automatically update with the new values. I have to go to the table/column with the index/match formula and click enter. Once I click enter, it'll ask me to search for the source file. I can either click on the source file or I can click cancel and the index/match will automatically update. There's no other pop up asking me for permissions or allow anything, so I am not sure what I should do so that this would allow for it to automatically update without having to go back and do this process every time. submitted by /u/GreenGloober [link] [comments]
- Links to a cell within current worksheet keep changing the location, or doesn't work at all.I have a series of titles at the top of my worksheet with links to the most popular categories, hopefully to get to those categories quickly for data entry. I right click on the cell where I want the link, select Link, the enter the cell reference I want to jump to, with the text to display. This works. It presents a link, and if i click on the link, it jumps to exactly where I want it to go. However, over time, the cell reference isn't correct, and jumps to something else. I assume this is happening due to modifications of the worksheet or what it's from. I do not use "$" in the cell reference, assuming that if I enter A720 as the cell reference, the reference will automatically be modified to A730 if 10 rows are added. I have enough of these links that I shouldn't have to modify all of them each time there is an insertion or deletion. I tried using the formula =HYPERLINK(A730, "Category1") or =HYPERLINK('Shawna Smith'!A730, "Category1"), and neither of these work at all. It does not jump to A730 when I click on it. What am I doing wrong, or should I be using another feature or formula? Thank you for your help. https://preview.redd.it/nb4q3dm883tg1.png?width=649&format=png&auto=webp&s=cfc0c9e7f2d7e9d620075eebf6def8e7500f09fa submitted by /u/KatMagic1977 [link] [comments]