What it does: The XLOOKUP() Function allows you to look up and return values in corresponding columns or rows (!!). I have an exclamation point there because in many ways this function could eliminate most of the need for Vlookup, Hlookup, and the Index/Match combination when used for that purpose.
Arguments: XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
What it does: THE INDEX() Function returns a value based on the array of values you select and the row and column within that array (column is optional).
Plain English: This formula will help quickly you locate values within an array. It is very often used with the MATCH formula to quickly match up two sets of data and return the values you need. A simple example would be looking up an Employee Name based on his/her ID Number. If you only had the ID, but also had a mapping table of ID -> Employee Name, this formula would quickly look up the ID and return the name of the employee you’re searching for.
Arguments: INDEX(array, row #, [column #])
- Array – The list of values you want to search through
- Row # – Which row within the Array do you want to search?
- [Column #] – Which column within the Array do you want to search? (optional)
Blog Posts using INDEX()
Blog Post about INDEX() and MATCH() used together:
Videos using INDEX()
What it does: The Hyperlink() function links the cell to a local location (another point in the spreadsheet, another tab, specific cell, etc..) or to an actual URL like Google.com.
Plain English: This function can be used to link to a very specific points in OTHER workbooks, the workbook you’re currently in, or most commonly to a live URL.
- Link – The link you want to visit (local or world wide web URL)
- Name – Optional – How you want the link to appear in the cell. Example: Google
Blog Posts using HYPERLINK()
Videos using HYPERLINK()