Excel XLookUp with single critera but multiple columns

Question

XLookup is capable of doing look-ups with multiple criteria using two methods, concatenation and another boolean array. Both are pretty straightforward and easy to use. I prefer the boolean array method and have used it a few times.

Most examples you find online will show something like this. You have a table of items, with the following columns. Item, Size, Color & Price. The look-up is then done by finding a Price based upon an item that matches a given Item,Size,Color combination as shown in this image.

I'm looking for a solution that allows for this. Given the example above, let's say we have striped items, so we have two colors, Color and Color2.

I'm looking to now match Item="Hoodie" AND Size="Medium" AND (Color="Blue" OR Color2="Blue")

Is this possible? The XLookup boolean array is doing a Bitwise AND comparison but I need a Bitwise OR.

Answer 1

So, just doing 2 columns for colors:

IFERROR(MATCH(D2,A3:A5,0),MATCH(D2,B3:B5,0))

Note, another iferror() is needed if the target color does not exist in either column, but that depends how you want index() to work.

Answer 2

So I found the answer by experimenting with different operators. What I found was that this function here.

To break down this function we have the following.

=XLOOKUP(1,(B5:B15=I5)(C5:C15=I6)((D5:D15=I7)+(E5:E15=I7)),F5:F15)

You can see we search 4 different arrays for 3 values. It's important to note that the last two arrays we search are enclosed in their own set of parenthesis and that the last two array searches are joined with a + character and not the * character. This results in an XOR join, instead of an XAND join.

So the logic could be written as this. Find an item where Item = "Hoodie" AND Size = "Large" AND (Color = "Blue" OR Color2 = "Blue")