![]() The lookup value must be on the left in the lookup table. ![]() It can look up values only from left to right. VLOOKUP is a great function, but it has its limitations. ![]() Mastering these functions will not only make you look like an Excel whiz to your colleagues and manager - it will also make tedious, mundane tasks quick and simple. INDEX and MATCH used in combination helps you extract the data you need from a large data set efficiently and precisely. Used separately, these functions are invaluable, but when you combine them, their true power is unleashed. It’s a fantastic time-saver when you need to input or change a lot of data quickly and accurately.Īside from VLOOKUP (which looks up the value in one column and returns a corresponding value from another column), INDEX and MATCH are the most widely used and most powerful tools in Excel for performing lookups. Or get it going manually by clicking Data > Flash Fill, or Ctrl+E.įlash Fill is like magic, and can be used in many different scenarios. If Flash Fill is turned on ( File Options, Advanced), just start to type the next product number in the cell below and Flash Fill will recognize the pattern and fill down the remaining product numbers for you. Now, this is much faster and will impress people.Įstablish the pattern by typing “00001” into the first blank cell. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, and so forth). You can easily discard the “ABC” using Flash Fill. ![]() Suppose you have a list of product numbers in the first 10 cells of column A, for example “ABC-00001” to “ABC-00010” and you only need the numbers after the “-”. Flash Fill automatically fills your data when it senses a pattern. Excel developed a mind of its own in 2013 with this feature. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |