Lookup lowest value
Alongside its many uses, the LOOKUP function can also call elements of an Excel spreadsheet connected to the lowest value. To get these values, we can adopt a formula that is operating on three different functions, including INDEX, MATCH, and MIN functions.
= Index (range, MATCH (MIN (vals), vals, 0))
The formula above, as we said earlier, operates on three separate functions. The MIN function can be used by you to return the lowest value in a named range. It checks all the values in each cell in that range and picks the one that is the lowest as its returned value.
Now that it has returned a particular “lowest value”, the formula inserts the value into the MATCH function, turning it to the lookup value for this function. In turn, the MATCH function will return the location of that value as it appears on the named range.
The value returned by the MATCH function moves straight into the INDEX function. The INDEX function then returns the corresponding value in an adjacent cell next to the lowest value.
Looking up the lowest value in Excel is easy with this formula and can be used in association with other formulas.
In our example, we’re going to consider five fraternities on a college campus with different populations of house members. Our job will be to find which of these houses has the lowest amount of members i.e. lookup which of the fraternities has the lowest value.
The image below shows us the population status of each fraternity, including the Kappa Gamma, Alpha Chi Alpha, Beta Sigma Phi, Alpha Tau Omega, and Delta Psi.
Figure 1. Excel showing the number of people in each fraternity.
In another column on the same spreadsheet, we need to have somewhere to return the value from our lookup activity. Based on our example, we’ve created a separate column, E2. You can name it anything relating to the project, as we’ve chosen here. For someone else, the ideal tag could just be Fraternity and another column beside it, lowest members.
Figure 2. Excel showing the column where the lookup value will be returned.
The next line of action would be to use our formula for looking up the lowest value.
= INDEX(A3:A7, MATCH(MIN(B3:B7), B3:B7, 0))
This formula used in the cell returns the fraternity with the lowest value. This turns out to be Beta Sigma Phi which has just 700 fraternity members.
Figure 4. Excel showing the lowest value from the lookup.