In this tutorial lets see how you can use XLOOKUP in Excel to find the postion of the Lookup Value in the Lookup Array. Doing this with MATCH and XMATCH is pretty easy but is it fun unless it is done in a more comlex way? :D
To start with here is a small data set to use:
Next we need to create a column containing the Brand values against which we will identify the position of each brand in the Lookup array.
Using XLOOKUP we are able to retrieve the lookup value from the lookup array
Now lets see how we can tweek the code to return the positions, if instead of return array we use some functions that generate numbers then that would also work.
We need to use ROW construct as the following one: ROW($B$3:$B$7)-ROW($B$3)+1
What ROW will do is it will return 1,2,3,4,5 for each cell.
Once XLOOKUP finds the value of F3 in the range B3:B8, it will then use that position to get the value from the return array which is {1,2,3,4,5}, if nothing is found then it will simply return "No Item"
Finally, to make the code more readable if you have Office 365 then you can use LET function.
