1

I am using Excel to do propensity score matching (not the best tool for this, I know, but that's the assignment).

I am using MATCH() to find the entry in colO (that's letter O) closet to the entry in colM. My call works properly in libreoffice, but not in Excel. All the values in colO and colM are probabilities (i.e., between zero and one inclusive).

=MATCH(M11, O$11:O$60, 1)

If the value in colM is zero, then MATCH() returns 28, which is also a zero in colO. If the value in colM is greater than zero, then MATCH() returns 50, which is the smallest non-zero value in colO.

Any ideas? I don't typically use Excel. Oh, and here's the top of these columns:

propensity  health w/o vaccine  propensity  match
0                  2           0.393115219   28
0                  1           0.280598404   28
0.747918388        0           0             50
0.843491818        0           0             50
0.707782271        0           0             50
0.829627573        0           0             50
0.515298454        0           0             50
0.464636469        0           0             50
0.705333684        0           0             50
0                  3           0.641132271   28
0                  4           0.366445099   28
0.590387706        0           0             50
0.859441341        0           0             50
0                  2           0.304260844   28
0                  3           0.725971617   28
Ram
  • 3,092
  • 10
  • 40
  • 56
Richard Herron
  • 9,760
  • 12
  • 69
  • 116

1 Answers1

1

When you use 1 or -1 (not zero) as the third argument of MATCH, column O needs to be sorted. If you want to find the closest match on an unsorted list, you need to use an array formula

=MATCH(MIN(ABS(M11-$O$11:$O$25)),ABS(M11-$O$11:$O$25),FALSE)

Enter an array formula with Control+Shift+Enter, not just enter. Excel will put curly braces around the formula.

The first argument finds the minimum difference between M11 and every value in Column O. The second argument is a complete list of all the differences. The MATCH matches that minimum value to the complete list and returns the row on which it's found. I get

3
3
15
15
15
15
1
1
15
3
3
10
15
3
3

Which at first glance looks correct. Change the $O$25 in the formula to the end of your data in column O.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • Thanks! I didn't know they had to be sorted. I needed the next lowest match, so I have to use an `IF()` instead of an `ABS()`. – Richard Herron Feb 04 '11 at 17:33