A regular, one-way XLOOKUP, is a fantastic tool, but sometimes you may find yourself needing to lookup a value in two different directions.
Contents:
Two-Way XLOOKUP
Formula
Here is the basic formula to perform a two-way XLOOKUP
= XLOOKUP(val1, val1_range, XLOOKUP(val2, val2_range, return_range))To better demonstrate which value should go to which range, let's look at an example.
= XLOOKUP(size, size_range, XLOOKUP(color, color_range, fruits))
This formula takes a size input, "Medium", and looks up that value in the size row a the top. In the second XLOOKUP, the input "purple", is looked up from the colors in the first column.
For the results, we've just selected the entire table of values that this lookup could pull from.
Explanation
The reason this formula works is due to XLOOKUP's built-in ability to return a range of values.
One XLOOKUP narrows the table down to a specific column, and the other XLOOKUP narrows that column, to a specific row. Giving an exact return.
= XLOOKUP(size, size_range, XLOOKUP(color, color_range, fruits))Let's break this formula down into the two different XLOOKUPs, and look at the same example.
The inside lookup, looks up the color, and returns the entire row as a result:
XLOOKUP(color, color_range, fruits)) {Blackberry, Passionfruit, Plum} ''Results
These results are then fed into the outer XLOOKUP, giving us something that looks like this.
= XLOOKUP(size, size_range, {Blackberry, Passionfruit, Plum})The outer lookup, then takes the other input, in this case the size "Medium", and returns it's position out of the set. So, medium would be in position (or column) 2.
Because "Medium" is seen as the second item or position, that same position number is taken from our list of results returned by the first XLOOKUP.
Out of these fruits, {Blackberry, Passionfruit, Plum}, Passionfruit is the second item, which is also the correct item to lookup.
To visualize it, the two-way XLOOKUP looks and operates a bit like this:
