How to use the XLOOKUP function in Excel
Aug 13, 2023
How to use the XLOOKUP function in Excel
0:32 - VERTICAL lookup examplehttps://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/fo …https://www.techonthenet.com/excel/in …https://www.techonthenet.com https://www.facebook.com/techonthenetcom https://twitter.com/tech_on_the_net https://www.pinterest.com/techonthene …
Content
0.48 -> xlookup is the next generation lookup
2.96 -> function in excel that has the
5.04 -> functionality of both the vlookup as
7.359 -> well as the hlookup without all of their
9.76 -> limitations
12.08 -> you can use the xlookup function to
14.24 -> perform either a vertical lookup or
16.24 -> horizontal lookup
18.96 -> the xlookup function does not require
21.199 -> the lookup value to be in the first
23.199 -> column or row of a table
25.279 -> and it can return a default value when a
27.359 -> match is not found instead of the pound
29.92 -> n a error
32.8 -> let's get started with our first example
34.96 -> which covers how to use the x lookup
36.88 -> function to perform a vertical lookup
40.16 -> if your data is organized into vertical
42.239 -> columns you can use the x lookup
44.48 -> function to search for a value in a
46.64 -> column of your table and return a
48.8 -> corresponding value from another column
52.96 -> here we have a table of data containing
55.12 -> order information
56.96 -> we can use the x lookup to locate an
59.44 -> order id based on a particular product
62.079 -> value
63.359 -> to get started let's begin by entering
65.439 -> the x lookup command
67.92 -> as you can see the xlookup function can
70.24 -> take up to six parameters the first
72.56 -> three are required and the second three
74.72 -> are optional
76.64 -> the first parameter we'll enter is the
78.64 -> value that we are trying to locate in
80.72 -> the table
82.24 -> in our example we want to search for the
84.24 -> product called pairs
87.04 -> the second parameter we'll enter is the
89.28 -> range of cells that contain the product
91.52 -> information
93.2 -> this is the second column of our table
95.439 -> so we'll enter the range b2 to b6
99.439 -> the third parameter we'll enter is the
101.439 -> range of cells that contain the value we
103.759 -> want to return
105.36 -> since we want to return the order id
107.52 -> associated with pairs we'll enter the
109.68 -> range a2 to a6
112.479 -> for the purposes of this tutorial we'll
114.56 -> only enter the first three required
116.479 -> parameters and not any of the optional
118.719 -> ones
119.84 -> now let's see what our xlookup function
121.84 -> returns
123.119 -> you should see the order id
125.24 -> 10252
126.84 -> appear 10252
129.599 -> is the order id that corresponds to the
132.08 -> product name pairs that we searched for
135.28 -> next let's cover how to use the xlookup
137.44 -> function to perform a horizontal lookup
141.2 -> let's go to sheet 2 and look at our same
143.599 -> order information transposed with the
145.92 -> data organized in rows instead of
148 -> columns
149.599 -> so again we'll start with our x lookup
151.519 -> command
152.8 -> for the first parameter we'll enter
154.64 -> 10250
156.72 -> which is the order id that we are
158.319 -> searching for
160.16 -> for the second parameter we'll enter b1
162.72 -> to f1 which is the range of cells that
165.44 -> contain the order id information
168.48 -> for the third parameter we'll enter the
170.4 -> range of cells that contains the value
172.48 -> we want to return
174.16 -> since we want to return the product name
176.239 -> associated with the order id 1 0 2 5 0
180.239 -> we will enter the range b2 to f2 now
184.56 -> let's see what our xlookup function
186.239 -> returns
187.92 -> you should see the product name grapes
189.68 -> appear
190.56 -> grapes is the product that corresponds
192.4 -> to the order id 1 0 2 5 0.
196.56 -> this covers a few simple examples of how
198.959 -> to use excel's xlookup function
201.519 -> if you would like to see more xlookup
203.44 -> examples or would like to download the
205.599 -> sample data we used for this tutorial
208 -> please visit our website at
209.36 -> techonthenet.com
211.68 -> if you found our tutorial helpful please
213.76 -> leave a like on this video and don't
215.68 -> forget to subscribe to our youtube
217.36 -> channel for more great excel tutorials
Source: https://www.youtube.com/watch?v=A0jLeIG_pJY
