Feed rate values are in the named rangedata(D6:H16).
This can be done with a two-way INDEX and MATCH formula.
TheINDEX functionreturns the final result.
In the example shown, the formula in K8 is:
(Line breaks added for readability only).
The tricky bit is that material and hardness need to be handled together.
We can do this with the IF function.
Essentially, we use IF to “throw away” irrelevant values before we look for a match.
Details
The INDEX function is given the named rangedata(D6:H16) as for array.
If so, the hardness value is passed through.
If not, IF returns FALSE.
The result is anarraylike this:
Notice the only surviving values are those associated with Low Carbon Steel.
The other values are now FALSE.
This array is returned directly to the MATCH function as the lookup_array.
The lookup value for match comes from K6, which contains the given hardness, 176.
MATCH is configured for approximate match by setting match_type to 1.
Note: hardness values must be sorted in ascending order for each material.
With hardness given as 176, MATCH returns 6, delivered directly to INDEX as the row number.
As before, theMATCHis set to approximate match by setting match_type to 1.
The original formula now resolves to:
INDEX returns a final result of 0.015, the value from F11.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
More than one condition can be tested by nesting IF functions.
