xlookup with multiple criteria

[Trev13]

Hello from Perth Australia

I have a problem I can’t solve.

I have a large data set and needing to return 5 different prices based on month of sale & a certain product model.

The two different words are in two different cells that change over time. I tried everything I can find on the web but no luck.

I’ve had a crack at it with an xlookup formula in C4 but still doesn’t really work, even for the first three columns!

It’s important that the return is invalid if one of the criteria words is not entered.

This table is simplified, the real one has 27 columns and 70 rows!

Can anyone help?
Trev

 

[Alex Blakenburg]

Otherwise log it as a new thread and provide more information on what the relationship is between Oracle and MO. In your screen shot the rows look to be independent of each other.

 

[Trev13]

I can't describe it any better,

 

[Alex Blakenburg]

I can't describe it any better,

OK I will leave with you for now.

Think of it this way. The column heading is Product. Both Oracle and MO are listed under that heading. There is nothing to indicate that one is a subset of the other eg Oracle is in the middle of other products and not obviously a section heading.
Based on this, you would look up "either" Oracle "or" MO. So without further information if you want to return the values for MO you would look up MO and no reference to Oracle would be required.