Hi, I’m trying to use an index-match formula, and the match portion is returning incorrect results.
MATCH($A5,$othersheet.$A:$A,0)
I’m trying to match ABCDE048158 but it’s returning the data for another similar line ABCDE048107.
In looking at a couple of the returned results, it’s as if the formula is checking only the first 9 characters for a match and returning the first result it finds.
Your example .ods file uploaded would help a lot to help.
I never experienced a related issue.
Your example may be misleading in a relevant way: MATCH() will interpret its first parameter as a RegularExpression or as a string probably containing “wildcards” if this is enabled under >Tools>Options>LibreOffice Calc >Calculate>>.
If your actual string in cell A5 contains one (at least) of the special characters of the “wildcard-syntax” (?,*) or one of the many more concerning RegEx, lots of strange things can happen.
I’ve uploaded a small test example that shows what is happening. The formula is in sheet1 column B and is normally used to index-match hundreds of columns on both sheets. The actual value in A5 is MRWOR048158 which doesn’t have any wildcards. I’m unfamiliar with RegularExpressions, but changing that setting mentioned seems to not change the output. Thanks for taking a look.
Hello,
have the index range to start in cell Sheet2.$A$1... or care about the header shift and use -1 for the row index, since using $A:$A in MATCH() evaluates a number relative to A1.
[1] =INDEX($Sheet2.$A$1:$B$15715;MATCH($A3;$Sheet2.$A:$A;0);MATCH(B$1;$Sheet2.$A$1:$B$1;0)) –or–
[2] =INDEX($Sheet2.$A$2:$B$15715;MATCH($A3;$Sheet2.$A:$A;0)-1;MATCH(B$1;$Sheet2.$A$1:$B$1;0))
Q302958-Test-INDEX-MATCH-Mod.ods
Hope that helps
Thank you yes that worked. I went with option two as the formula would have used named ranges in excel with data and a header $Sheet2.$A$2:$B$15715=cDATA and $Sheet2.$A$1:$B$1=cHEAD.
Could you explain the “why” of the correction? I’m really struggling to shift from Excel to LO and would like to understand the mechanics. Thanks again for the assist.
You are using MATCH() to look in $A:$A which is an abbreviation for A1:A1048576. The result is the actual row, where the value can be found (let’s say for example it is row 1000). Now you put that value into INDEX(), whose range has a starting point of A2 (which is row 1 of the index range; indices in INDEX() are relative to the upper left cell of the range). Now 1+1000=1001 and you get the value in row 1001 of Sheet2, but you want that on row 1000. Therefore you need to subtract 1 from the row index to get the row 999 relative to A2 (which is row 1000 relative to A1).
Make a sample yourself to understand “relative to the upper left cell of the range”:
Set cells
A11: Value 1
A12: Value 2
..
A20: Value 10
Now use in any other cell: =INDEX(A11:A20;5;1) and you get the value of the 5th cell in A11:A20, which is Value 5
Fantastic, that was very helpful, thank you!