Hi guys, Welcome to the Advanced Video Search Engine, and you are watching Excel - Performing a VLOOKUP to the Left in Excel: INDEX and MATCH Tutorial - Episode 487. That video is uploaded by MrExcel.com at 2009-05-01T08:08:08-07:00. We are promoting this video only for entertainment and educational purpose only. So, I hope you like & enjoy it. [UQYevoO1Z50]
| Name | Excel - Performing a VLOOKUP to the Left in Excel: INDEX and MATCH Tutorial - Episode 487 |
| Video Uploader | Video From MrExcel.com |
| Upload Date | This Video Uploaded At 01-05-2009 15:08:08 |
| Video Discription | Microsoft Excel Tutorial: Performing a VLOOKUP to the Left in Excel: INDEX and MATCH Tutorial. Welcome back to the MrExcel netcast! In our previous videos, we've covered the basics of VLOOKUP, INDEX, and MATCH. Today, I'm going to show you another useful application of INDEX and MATCH when the table array is not formatted in the traditional way. In this example, we have a list of SKUs and their corresponding descriptions and prices. However, the price is in the wrong column for our VLOOKUP formula to work. While the easy solution would be to just move the price column, sometimes that may not be possible due to external factors. So, is there a way to perform a VLOOKUP to the left? Unfortunately, Microsoft has not yet provided a solution for this, but fear not, we have a workaround using INDEX and MATCH. Instead of using VLOOKUP, we will use the MATCH function to find the row number of the SKU we are looking for. This is done by specifying the lookup value, the range to search in, and setting the match type to exact (FALSE or 0). The MATCH function will return the row number of the SKU we are looking for. Next, we use the INDEX function to retrieve the price from the corresponding row and column. It's important to note that both ranges (SKU and price) must start on the same row for this to work correctly. One thing to keep in mind when using INDEX and MATCH is that the column number is optional, especially when there is only one column in the original range. However, it's important to double check that both ranges start on the same row to avoid any errors. With this method, we can easily perform a VLOOKUP to the left and retrieve the correct price for any SKU. Thanks for watching and be sure to check out our other netcasts for more Excel tips and tricks! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ To finish up the recent discussion of VLOOKUP, INDEX, MATCH, and OFFSET, todays episode talks about the strange situation where the data that you want is located to the left of the key field in the lookup table. Episode 487 will show you how to use VLOOKUP and MATCH to solve this problem. This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book! Table of Contents: (00:00) New use for INDEX and MATCH (00:34) VLOOKUP for description but Price is to left (01:03) Unintuitive solution (01:24) Using MATCH with INDEX function (02:15) Important note for creating INDEX and MATCH (02:39) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Column number Column placement Data validation drop-down Description INDEX and MATCH INDEX function MATCH function Price Row number Same row range requirement Table array formatting VLOOKUP VLOOKUP left Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152389/ UQYevoO1Z50 |
| Category | Education |
| Tags | Excel | business | accounting | spreadsheets | tutorial | technology | MrExcel | VLOOKUP | MATCH | Formula | Bill Jelen | Learn Excel | Excel Tutorial | Power Excel | Microsoft Excel | Excel Tips | Data Set | Excel Help | Learn Excel with MrExcel | Excel Tips and Tricks | MrExcel Podcast | mrexcel channel | mrexcel website | mrexcel forum | excel podcast | excel guru |
