The product in H4 is an input that can be changed at any time.
All data exists in an Excel Table nameddata.
However, this method only works for the first match.
In both formulas, MATCH returns 1 because the product is “A”.
As mentioned, this is a standard tool of INDEX and MATCH.
For a complete overview, see:How to use INDEX and MATCH.
The other row numbers have been replaced by FALSE.
With 1 in cell G7, SMALL returns the smallest value, which is 1.
This is returned directly to the INDEX function asrow_num.
At each new row, the formula returns the next nth match.
Theexample hereshows one approach.
Multiple criteria
To add multiple criteria, you useboolean logic, asexplained in this example.
you’ve got the option to use INDEX to retrieve individual values, or entire rows and columns.
from a set of numeric data.
More than one condition can be tested by nesting IF functions.
