0

I'm using the Excel MATCH function, and it works if the lookup_value is an unmodified number, but it does not always work if the lookup_value is a calculated number.

To be more specific:

My spreadsheet can ask the user whether or not she would like to use a recommended value for particle size. If she chooses "No", she has the option of inputting her own value. She can input the value in either imperial units, or metric units. There is a cell for an imperial input and a cell for a metric input. (She can choose which she fills in).

Because all calculations must eventually be done in imperial units, the final particle size box takes the user specified value and either keeps it the way it was (if it were entered as imperial), or converts it back to imperial (if it were entered in metric). It does so using this equation: 

=IF(AND(S24="No",U25=""),S25,IF(AND(S24="No",U25<>""),U25/25.4,S23))

Where S24 holds the decision whether or not to use the recommended value, U25 holds the metric input, S25 holds the imperial input, and S23 holds the recommended value. The conversion is from mm to in.

The final particle size is then called by another sheet and used in the MATCH function. Certain input values (from a drop down menu) in the metric input cell result in a #N/A error from the MATCH function.

I have tried removing extraneous spaces, formatting all cells involved to be Numbers with 3 decimal places, and putting the conversion from metric to imperial directly into the lookup_value cell (e.g. instead of pulling the final particle size, I entered the following in the lookup_value cell, but it still could not find 0.375 in the lookup_array)

=9.525/25.4

Does anybody know why the MATCH function is only working for some of the lookup_values even though they are just conversions that only entailed multiplying each imperial value by 25.4 then dividing that value by 25.4?

Ram
  • 3,092
  • 10
  • 40
  • 56
arlederman
  • 15
  • 1
  • 1
  • 5
  • 2
    http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems => round both the input number and if the target numbers are calculated as well, you need to round them as well - `=ROUND(9.525/25.4, 3)` - and no, when you change number format to only display 3 digits, that does not change the value at all – Aprillion Feb 09 '15 at 08:11

1 Answers1

1

The Match() function has one important parameter: the last one. It is either 0 or 1. If it is 0, then Match will find only an EXACT match. In your scenario, when you convert numbers between different systems, the resulting number may not be an exact match.

You may want to use rounding or other techniques to ensure that the first parameter of the Match() function is indeed in the list of values that you feed into the Match function.

Formatting to a specific number of decimals is not good enough. The number Match is looking for must be an exact match in VALUE, not in looks.

You may want to apply rounding instead of formatting for better results.

This is as much as I can say without seeing a data sample. Edit your question and include a file (or a link to a file on a public sharing site). Then I can get more specific.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • To be a little more precise, the last parameter of MATCH can be 0, 1, or -1. Depends on whether you want to round up or down in a sorted list [also to clarify - the list must be sorted in ascending or descending order, depending on your purposes - otherwise you must use MATCH(TERM, ARRAY,0)]. – Grade 'Eh' Bacon Aug 11 '15 at 18:44