I have some very strange MATCH() behavior. I've narrowed it down to a very simple case. In the attached image, columns A-D are all just constants. F1 is =LARGE(A1:D1,1) and G1 is =MATCH(LARGE(A1:D1,1),A1:D1), and those formulas are copied down the columns.
You can see that the LARGE() in col F is working just fine. For example, F1 says the largest value is 1.0263. But G1, which contains =MATCH(LARGE(A1:D1,1),A1:D1), says column 4 matches that largest value!? G2:G6 are correct, then G7:G9 are wonky again, and so on. In all those cases, column 1 is actually the largest value.
It also sometimes selects the wrong column if I ask for the 2nd-largest value. If I ask for 3rd-largest I get #N/A in rows 1 and 7-9 (same as the erroneous rows before). If I ask for 4th largest, all cells are #N/A except row 11, which correctly says column 4 is 4th largest.
Am I doing something obviously wrong here ??
You can see that the LARGE() in col F is working just fine. For example, F1 says the largest value is 1.0263. But G1, which contains =MATCH(LARGE(A1:D1,1),A1:D1), says column 4 matches that largest value!? G2:G6 are correct, then G7:G9 are wonky again, and so on. In all those cases, column 1 is actually the largest value.
It also sometimes selects the wrong column if I ask for the 2nd-largest value. If I ask for 3rd-largest I get #N/A in rows 1 and 7-9 (same as the erroneous rows before). If I ask for 4th largest, all cells are #N/A except row 11, which correctly says column 4 is 4th largest.
Am I doing something obviously wrong here ??