![use vlookup excel use vlookup excel](https://2aih25gkk2pi65s8wfa8kzvi-wpengine.netdna-ssl.com/excel/files/2017/12/VLOOKUP-Photo-1-1024x350.jpeg)
We first specify the column that has the value we wish to return. When using the INDEX/MATCH combination, the thinking reverses. We think about the VLOOKUP function like this: go find this, comma, in here, comma, return this. VLOOKUP begins with the value we are trying to find. ** The thing that can be often confusing for long-time VLOOKUPers is the order of the arguments. Wow, when I read Richard Schollar’s post on Bill Jelen’s site, I was so impressed! Well done! * There is a very creative way to have the VLOOKUP function go left, which is to use the CHOOSE function as the second argument.
#USE VLOOKUP EXCEL DOWNLOAD#
If you want to play with these functions a bit, feel free to download the IndexMatch Excel file that was used to prepare the screenshots for this post: The INDEX/MATCH combination is quite handy, and can be used to return values that lie to the left of the lookup column. $C$16:$C$27 is where we are looking, the CustID column.B7 is the value we are trying to find, our CustID.MATCH(B7,$C$16:$C$27,0) determines the row for the INDEX function.$B$16:$B$27 is the list that contains the value we wish to return, the CustName column.For the row_num argument, we used the MATCH function to determine the relative position of the CustID within the CustID column.** We used the INDEX function to return a value from the CustName column. The MATCH function returns the relative row number to the INDEX function. Where the MATCH function figures out the row number argument. Our INDEX/MATCH formula will look something like this: =INDEX(array, MATCH(.)) What we’ll do is use the INDEX function to return a cell value, and we’ll nest the MATCH function in there so that it can tell the INDEX function which row has the value we want. is the optional column number, useful when performing two-dimensional lookups.row_num is the relative row position that has the value you want to return.array is the range that has the value you want to return.The syntax of the INDEX function follows: =INDEX(array, row_num, ) Technically, it does much more than that, but since we are just getting warmed up, let’s stick with that idea: it returns a cell value from a list at a given position. The INDEX function returns the cell value from a range at a given position.
![use vlookup excel use vlookup excel](https://www.windowscentral.com/sites/wpcentral.com/files/styles/xlarge/public/field/image/2020/10/vlookup-search-function-excel_2020.jpg)
Just remember, the MATCH function returns the relative position of a list item. We are trying to find the relative position of our month (C6), in the list of month abbreviations (B11:B22), and zero for exact match. In our screenshot, we use the following function to return the relative position of the month name: =MATCH(C6,B11:B22,0) is typically 0 for exact match, but there are other choices to explore here.lookup_value is the value we are trying to find.The syntax of the MATCH function follows: =MATCH(lookup_value,lookup_array,) This idea is illustrated in the screenshot below. If we asked Excel to MATCH “Jun” in a list of month abbreviations, it would return 6. The MATCH function returns the relative position of a list item. Since the arguments are range references, Excel adjusts the formulas to accommodate column inserts. The INDEX/MATCH combination does not suffer from this limitation. Since the third argument is expressed as an integer, it will not adapt to column inserts when inserted between the lookup and return columns. When the third argument of VLOOKUP is expressed as a static integer value, such as 3, a column inserted between the lookup column and the return column will break the function. In addition to being able to return values to the left, this combination doesn’t suffer from another common limitation of the VLOOKUP function. VLOOKUP is a righty, can’t go left.*Īnd this brings us to the reason we are here, to get the hang of using the INDEX/MATCH combination to return values that lie to the left of the lookup column. That is, the return column lies to the left of the lookup column. The problem is that in the lookup range, the CustName column lies to the left of the CustID column. We would like to look up the CustID from the Customers table, and return the CustName. We are trying to populate the report’s CustName column. Note: depending on your version of Excel, you may have XLOOKUP as an option … more info here: Overview This is a perfect time to move beyond the VLOOKUP function and explore the INDEX and MATCH functions. If you have ever tried to return a value that physically lies to the left of the lookup column, you quickly realize that this task is difficult to accomplish with the VLOOKUP function.