The FILTER function can be thought of as the new power lookup function. To get the Profit, we write the following formula ( in cell F5): =XLOOKUP(F3, A4:A13, C4:C13) #4 – The FILTER Function We have also included an argument that issues a text response if the item being searched for is not found in the list. This would be impossible with a traditional VLOOKUP function ( without performing some crazy in-memory, virtual table construction which only 9 people on planet Earth find enjoyable.) Notice that in the data, the column that we are returning data from is to the LEFT of the column we are searching. To get the Division, we write the following formula ( in cell F4): =XLOOKUP(F3, I4:I13, H4:H13, "Missing") Did you see the hidden awesomeness? From this selection, we want to return the associated Division from column H and the associated Profit from column C. Our objective in the following example is to select an App from the dropdown list located in cell F3. If you wish to sort by more than one criterion ( i.e., primary ascending sort by Name, secondary descending sort by Salary), array notation will be required to define the multiple “sort_index” and “sort_order” levels. To invoke this argument, enter the value TRUE. This is an option that you will likely never use or use infrequently at best. This would be used when your data is set up in the opposite format rows are categories and columns are transactions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |