The message may read, “The member you are looking for is not registered in our database.” If the XLOOKUP cannot find a value, it returns a default or a customized message. Optional arguments If not found (XLOOKUP only)
#HOW TO USE VLOOKUP IN EXCEL WHAT GOES IN EACH CODE#
BUT, if you don’t hard code the column number in the VLOOKUP, but manage to make this argument the result of a formula, the VLOOKUP will provide a benefit that may be hard to replicate with an XLOOKUP alone. This is where users rave about the XLOOKUP and are ready to dispose of the VLOOKUP immediately. The XLOOKUP is harder to break because the two (lookup and return) arrays are relative and not hardcoded into the formula.The lookup array can be to the right or left of the return array.
![how to use vlookup in excel what goes in each how to use vlookup in excel what goes in each](https://blog.hubspot.com/hs-fs/hubfs/vlookup-range-lookup.jpg)
The past two arguments (the separation of the ranges) indicate the biggest benefits to the XLOOKUP: You will have to use two VLOOKUPs to manage the same. The return array in the XLOOKUP can also accommodate more than one return column in the return array, which will return more than one value related to the lookup value. This range has to be aligned with the lookup array (previous argument) in length and position to avoid a #VALUE! error. The XLOOKUP requires an entire range of values for this argument to indicate the array where the return value may be found. The VLOOKUP is easy to ‘break’ if this column number is hardcoded into the argument, which is an important benefit to the XLOOKUP. In a VLOOKUP this is a single number that refers back to the table array (the previous argument that indicated the entire range where the search value and returned value can be found). In our example, this would be the first name of the member. This argument indicates the range where the returned value can be found. Column index number (VLOOKUP) / Return array (XLOOKUP) The lookup array can now be to the left or the right of the return value column and the XLOOKUP can act as a VLOOKUP or an HLOOKUP. The separation of the arrays makes the XLOOKUP much more flexible than the VLOOKUP. The lookup array should house the value Excel will search for and the return array argument will house the value to be returned.
![how to use vlookup in excel what goes in each how to use vlookup in excel what goes in each](https://www.excelcampus.com/wp-content/uploads/2014/05/Excel-Vlookup-Explained-Where-Table-Array.png)
The XLOOKUP separates the lookup array and the return array into two arguments. The next argument (return array) refers back to this array by indicating the relevant column number of the table array that houses the return value.Įxcel is programmed to search for the lookup value in the left column of this range and return a value to the right of this, indicated by the column number from the next argument. The VLOOKUP uses table_array, this area indicates the column for the lookup value and stretches to include the column for the returned value. This represents the biggest difference between VLOOKUP and XLOOKUP and in the example above will be the member’s dataset where you would find both the member (lookup value) and the member’s first name or last name (returned value).
![how to use vlookup in excel what goes in each how to use vlookup in excel what goes in each](https://blog.hubspot.com/hs-fs/hubfs/vlookup-value.jpg)
Table array (VLOOKUP) / Lookup array (XLOOKUP) the member ID). Both VLOOKUP and XLOOKUP usually lookup (search for) a single value. The value you want to find on the new list (e.g.