Ok, I have a weird problem.
I'm using MATCH() and INDEX() to dynamically generate some lookups in a table. I *thought* this was working fine, but have just comes across a weird problem and I can't work out whats wrong.
The formula I'm using is this:
It's a bit complicated, so I'll break it down to make reading easier:
BCI_Proforma!H25 is a cell that contains an error code.
BCI_Proforma!F25 is a cell that contains a number relating to the severity of that error code.
I have a series of error codes listed as decimal values, ie error 1.1, 1.2 - 3.1, 3.2 - 10.1, 10.2 etc. This bit of the code converts them from a number to an equivalent text string with a fixed number of decimal places.
This gives me the row number of the previously determined value within a column $E$12:$E$72, where the block of cells listed is the error code that I'm looking for. Note these are hard coded, so there isn't any problem with inserting extra ones, or someone changing the numbers so the MATCH type selected (1) won't work.
The error codes also have an associated severity, 1 thru' 5, and the value I need depends on both the error and the severity. Hence, this give me the column that relates to the severity specified.
Using these two numbers, I can then INDEX the entire table to get the value I want, right?
Wrong.
Breaking this down into separate bits of code so I can find out whats going wrong suggests that the issue is in the first MATCH() function - the one that selects the row that contains the error. It's fine as long as the row number it needs to return is 30 or less. As soon as the matching number is in row 31 or above, it falls over and returns the value of the last row of the array.
This seems to be completely independent of the trailing zero as a result of the FIXED() function - it works fine elsewhere.
So, if the error is 7.1 and the FIXED() result is "7.10" (in row 28), 7.2 (in row 29) or 8.1 (in row 30), the MATCH() function returns 28, 29, 30 respectively. However, if the error is 8.2 (in row 31), the MATCH() function returns 61.
Now, before someone suggests that I change something else to dodge this issue - I should note I've already changed the decimal error codes to a text error code (swapped the . for -) and this means the sheet works as it should - however, as the cause of the problem seemed to me to be something else, I'm still asking the question anyway as something similar might crop up again where such a simple dodge (not a fix) might not work.
Anyone got any ideas?
I'm using MATCH() and INDEX() to dynamically generate some lookups in a table. I *thought* this was working fine, but have just comes across a weird problem and I can't work out whats wrong.
The formula I'm using is this:
Code:
=INDEX(BCI_defects_tables!$E$12:$J$72,MATCH(FIXED(BCI_Proforma!H25,2,TRUE),BCI_defects_tables!$E$12:$E$72,1),MATCH(BCI_Proforma!F25,BCI_defects_tables!$E$12:$J$12,1))It's a bit complicated, so I'll break it down to make reading easier:
BCI_Proforma!H25 is a cell that contains an error code.
BCI_Proforma!F25 is a cell that contains a number relating to the severity of that error code.
Code:
FIXED(BCI_Proforma!H25,2,TRUE)I have a series of error codes listed as decimal values, ie error 1.1, 1.2 - 3.1, 3.2 - 10.1, 10.2 etc. This bit of the code converts them from a number to an equivalent text string with a fixed number of decimal places.
Code:
MATCH(FIXED(BCI_Proforma!H25,2,TRUE),BCI_defects_tables!$E$12:$E$72,1)This gives me the row number of the previously determined value within a column $E$12:$E$72, where the block of cells listed is the error code that I'm looking for. Note these are hard coded, so there isn't any problem with inserting extra ones, or someone changing the numbers so the MATCH type selected (1) won't work.
Code:
MATCH(BCI_Proforma!F25,BCI_defects_tables!$E$12:$J$12,1)The error codes also have an associated severity, 1 thru' 5, and the value I need depends on both the error and the severity. Hence, this give me the column that relates to the severity specified.
Using these two numbers, I can then INDEX the entire table to get the value I want, right?
Wrong.
Breaking this down into separate bits of code so I can find out whats going wrong suggests that the issue is in the first MATCH() function - the one that selects the row that contains the error. It's fine as long as the row number it needs to return is 30 or less. As soon as the matching number is in row 31 or above, it falls over and returns the value of the last row of the array.
This seems to be completely independent of the trailing zero as a result of the FIXED() function - it works fine elsewhere.
So, if the error is 7.1 and the FIXED() result is "7.10" (in row 28), 7.2 (in row 29) or 8.1 (in row 30), the MATCH() function returns 28, 29, 30 respectively. However, if the error is 8.2 (in row 31), the MATCH() function returns 61.
Now, before someone suggests that I change something else to dodge this issue - I should note I've already changed the decimal error codes to a text error code (swapped the . for -) and this means the sheet works as it should - however, as the cause of the problem seemed to me to be something else, I'm still asking the question anyway as something similar might crop up again where such a simple dodge (not a fix) might not work.
Anyone got any ideas?
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")