Hello and in this tutorial let us go over the X-lookup with realistic examples.
We are starting with a very basic example of X-lookup, we are looking for Sandra Lang's Department
So, we will type in the green area =X-lookup (Click on Sandra's name as this is your lookup value or who are you looking for?
Next, we will select the column where the lookup value is among the others, column B in this example
Now the return value is basically saying what value do you want back? In this case I want the Department
Tip: Remember that unlike V-Lookup, I don't need to select the entire tables and add the index value
X-lookup already knows what values we are looking for by selecting the lookup value and return value columns
Also, in the past the V-lookup scans the data from left to right, x-lookup can do it right to left too, here is an example. Let’s see which department the Chef position belongs to, this is a right to left search
In the next example we see the record of Jose Fuentes, and we will change our X-lookup with another feature not available in the V-Lookup, you can make custom messages if the data is not available for example in this case we will use the X-lookup to get the hire date of Jose, so we will repeat the same X-lookup but this time, we want to add a message "Not Valid" if the employee does not exist
Now let us test the formula to see if it works, let's change the name to Marco Polo and probably you will now see the "Not Valid" field instead, that is good
So in this training time flew and we have new staff and some of the previous employees are now in different titles. So, we will go ahead and get the most recent role for Ava Logan.
In this list the information is not sorted by date so I will go ahead and do it to show you the next example
Let's click on the hire date column, press sort in the Home Tab and then press sort from oldest to newest
Let's get Ava's latest role by typing =XLOOKUP(G12,B12:B24,E12:E24,"NA",0,-1)
Notice that this time we typed -1 and that will let us search from last to first
Let's do another example with Lucy Chan but this time, we are displaying both the Department and the Role
In this last table we have the employees’ salaries, so we are going to combine the Department, Role and Salary information, while we can copy and paste let's use the X-lookup again
This time we are calling the employee, and we want in return the Role, let's write “na" and then click 0 for an exact match
We also we'll go over the wildcard search
Notice the “*”&LookupValue&”*” so Excel knows it could start and end with any character. Also, we added the 2 after “na” in the X-lookup code so we could tell Excel this is a wildcard search.
Let's say that we want to pull the results for those whose last name starts with mills
Please let us know your comments below, we hope you found this explanation about X-lookup informative and clear for your tasks.
Comentários