Forum Discussion
Assign a value based on two columns of criteria that each contain wildcards?
I am trying to assign a sales code to each sales line based on the customer and/or product code. So, I want to assign a sales code based on either 1. the customer number, 2. the product code or 3. both the customer number and product code. If the combination is not found in the table array, then I want the sales code to equal the product code. I have tried Index Match, If, Filter & XLookup but I can't get them to work with the blank cells. I have tried "*" in the table array, in the formulas, and in helpers, but I keep getting errors.
Here's an example of what the sales code should be. The blanks in the table array are wildcards. A* does not work.
Thanks for your help!
4 Replies
select * from lookup_in_keywords;
select * from lookup_in;
select a.*,b.F_C from lookup_in_keywords a left join lookup_in b where a.f01 like b.F_A or a.f02 like b.F_B;
how about this by sql?
=IFERROR(IFERROR(IFERROR(INDEX($C$2:$C$7,MATCH(1,($A$2:$A$7=A10)*($B$2:$B$7=C10),0)),INDEX($C$2:$C$7,MATCH(A10,$A$2:$A$7,0))),INDEX($C$2:$C$7,MATCH(C10,$B$2:$B$7,0))),C10)You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
OliverScheurich Thank you! I plugged it into my sheet and it looks like there is one scenario I am getting an incorrect sales code for. The first two rows need to be "and" but its calculating as "or". So if the data in my lookup table has one or the other, its giving me the sales code, but I need to original product code (since both criteria are not true). Hopefully that makes sense!
=IFERROR(IFERROR(IFERROR(INDEX($C$2:$C$3,MATCH(1,($A$2:$A$3=A10)*($B$2:$B$3=C10),0)),INDEX($C$4:$C$5,MATCH(A10,$A$4:$A$5,0))),INDEX($C$6:$C$7,MATCH(C10,$B$6:$B$7,0))),C10)You are welcome. This formula returns the intended result in my sheet. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.
