In Microsoft Excel, you can use the MATCH function to find out the position of an item in a specific section. You can also customize the search using specific comparison types.
Here are more tips about controls and formulas in Excel.
Excel: Use the COMPARE function
The COMPARE function gives you the answer, at which position within the search matrix the element is located. No distinction is made between upper and lower case. Such a function in Excel is always structured according to the following scheme:
=VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])
The search criterion is the value or the element that you want to search for. This can be a number, a text module or a logical value. It can also be a cell reference. However, the cell referred to must then contain one of the elements mentioned..
The search matrix is the area in which to search. Simply enter the cell reference here, for example "A1, A2, A3, A4". If you want to search through several cells at a time, you can also put a colon between the first cell name and the last cell name, for example "A1: A4".
The comparison type is optional. Here you can enter the way in which the values within the search matrix are to be compared with the search criterion:
- If you write down 1 or enter nothing at all, Excel uses the MATCH function to find the largest value that is equal to the search criterion. If no value is found that matches the search criterion, the next lower value is given. The search matrix must be arranged in ascending order, for example "-3, -1,0,1,2, A, B, FALSE, TRUE".
- If you enter 0 as the comparison type: Here the COMPARE function searches for the first value within the search matrix whose value exactly matches the search criterion. The values in the search matrix can be sorted as desired. If there is no value in your search area that exactly matches, you will receive the error message "#NV".
- If you write -1 as the comparison type in the function, Excel searches for the smallest value that is equal to the search criterion. If no value is found that exactly matches the search criterion, the next higher value is given. The search matrix must be kept in descending order, for example "TRUE, FALSE, B, A, 2,1,0, -1, -3".
The MATCH function is very similar to the LOOKUP function in Excel. When using it, it is important whether you need the element itself or just its location. If all you need is the position of the item in the list, the MATCH function is the right tool for you..
