How to use the MATCH function in Excel
How to use the MATCH function in Excel
Learn how to use the MATCH function in Microsoft Excel. This tutorial demonstrates how to use Excel MATCH with our easy to follow examples and takes you step-by-step through the different options when entering your formula.
MATCH Function (and sample data):
https://www.techonthenet.com/excel/fo…
Excel Functions/Formulas:
https://www.techonthenet.com/excel/fo…
Excel Tutorials:
https://www.techonthenet.com/excel/in…
Don’t forget to subscribe to our channel for more great Excel tutorials!
Visit us at: https://www.techonthenet.com
or follow us on:
Facebook: https://www.facebook.com/techonthenetcom
Twitter: https://twitter.com/tech_on_the_net
Pinterest: https://www.pinterest.com/techonthene…
Content
0.32 -> the match function is one of the lookup
2.399 -> or reference functions in excel
4.88 -> the match function searches for a value
6.96 -> in an array and returns the relative
9.04 -> position of that item
11.519 -> this function can be used to either find
13.519 -> the position of an exact match
15.759 -> or the position of the closest match if
17.84 -> an exact match is not
19.199 -> found you can also use wildcards with
22.48 -> the match function
24.48 -> let's take a quick look at how to use
26.24 -> the match function in excel
28.64 -> here we have four columns of data in our
31.679 -> first example let's use the match
33.52 -> function to see if there is an exact
35.44 -> match for order number
37.12 -> one zero five seven four
40.879 -> to get started let's begin by entering
42.96 -> the match command
45.039 -> as you can see the match function takes
47.039 -> a number of parameters
49.44 -> the first parameter we will enter is the
51.52 -> value that we want to search for
53.12 -> in the array in this tutorial we will
55.92 -> enter the order number 10574
59.84 -> the second parameter is the array or
62.16 -> range of cells
63.359 -> that contains the value that we are
64.96 -> searching for
66.64 -> here we will enter the range a2 to a6
69.6 -> which contains all of the order numbers
72.799 -> finally and most importantly is the
74.479 -> third parameter which is the match type
77.439 -> this parameter determines whether we
79.2 -> want to find an exact match
80.96 -> or the closest match since we want to
83.759 -> look for the first value
85.36 -> that is equal to order number 10574
88.799 -> we will enter 0 as our match type
90.88 -> parameter
92.56 -> now let's see what our match function
94.159 -> returns
95.759 -> you should see the value 5 appear in
97.84 -> cell f2
99.439 -> this means that the match function found
101.28 -> order number 10574
103.759 -> in the fifth position in the range a2 to
106.399 -> a6
107.92 -> but what happens if a match is not found
110.96 -> let's instead search for order number
112.88 -> 105
119.04 -> you should see the value not available
121.04 -> error code appear in cell
122.799 -> f2 this means that the match function
125.84 -> did not find
126.719 -> order number 10572 in the list of order
130.239 -> numbers
131.2 -> in the range a2 to a6
134.4 -> next let's modify our formula to find
136.48 -> the largest order number
138.16 -> that is less than or equal to one zero
140.4 -> five seven two
142.64 -> for this example the array must be
144.56 -> sorted in ascending order
146.4 -> which means that for the range a2 to a6
149.44 -> the smallest order number must be found
151.519 -> in cell a2
152.879 -> and the largest order number must be in
154.879 -> cell a6
157.2 -> so we will start with the same formula
159.04 -> as last time and enter
160.8 -> 1 0 5 7 2 as the first parameter
164.879 -> a2 to a6 as the second parameter
168.56 -> but this time we will enter 1 as the
171.04 -> third parameter
173.12 -> this will find the largest value that is
175.44 -> less than or equal to the value you are
177.2 -> searching for
178.239 -> which is order number 10572
182.159 -> now let's see what the match function
183.76 -> returns this time
186 -> you should see the value 3 appear in
188 -> cell f5
190.239 -> this is the position in the range a2 to
192.64 -> a6
194 -> where we have found the largest value
196 -> that is less than or equal to the order
197.68 -> number
198.08 -> one zero five seven two so in this case
201.44 -> the order number
202.48 -> one zero five seven one was the match
205.44 -> which is a relative position of three
208.879 -> finally let's modify our formula to find
211.36 -> the smallest order number
212.959 -> that is greater than or equal to one
214.799 -> zero five seven two
217.28 -> this formula we will enter on sheet two
219.84 -> because the array must be sorted in
221.599 -> descending order
223.28 -> this means that for the range a2 to a6
226.4 -> the largest order number must be found
228.239 -> in cell a2
229.599 -> and the smallest order number must be in
231.68 -> cell a6
233.68 -> so we will start with the same formula
235.439 -> as last time and enter
237.48 -> 10572 as the first parameter
241.04 -> a2 to a6 as the second parameter
244.879 -> but this time we will enter -1 as the
247.439 -> third parameter
249.599 -> this will find the smallest value that
251.76 -> is greater than or equal to the value
253.519 -> you are searching for
254.959 -> which is order number 10572
258.959 -> now let's see what the match function
260.56 -> returns
262.32 -> for this example you should see the
263.84 -> value 2 appear in cell
265.68 -> f2 this is the position in the array a2
269.199 -> to a6
270.24 -> where the smallest value is greater than
272.32 -> or equal to the order number
274.44 -> 10572 so in this case the order number
278.199 -> 10573 was the match
281.36 -> this completes our example of how to use
283.36 -> excel's match function
285.28 -> if you would like to see more match
286.88 -> examples or would like to download the
288.88 -> sample data we used for this tutorial
291.12 -> please visit our website at
292.68 -> techonthenet.com
295.199 -> if you found our tutorial helpful please
297.28 -> leave a like on this video
298.96 -> and don't forget to subscribe to our
300.479 -> youtube channel for more great excel
302.479 -> tutorials
Source: https://www.youtube.com/watch?v=9dbxi5ROskQ