I am sure this may be a bit confusing, but I have a database similar to this screenshot:
What I want to do is, based on the data on “Info” ranges, apply a value to the Value ranges. For that I had formulas such as =if(A1 = "A", 1, 0) or =if(and(B1 = "red", C1 = "pear"), 1, 0) which would get me the second screenshot:
My problem is, I need to have these criteria as something easy accessible and modifiable for the user, for that I made another sheet that has the criteria, like the third image.
Another problem I can have is that, instead of getting just fruits in “Info3”, I might get say “pear juice”, and I would need that to be considered as “pear” too.
I first thought of using XLOOKUP to build a formula that would check if its line fit any of the criteria listed on the other table and then pull the value from the respective Value range, but I couldn’t think of a way to have it work with the ones with complex criteria, like the “red and pear”
I know that you can use a XLOOKUP with multiple criteria using something like this:
=XLOOKUP($A2&$B2&$C2,Criteria!$A$2:$A$3&Criteria!$B$2:$B$3&Criteria!$C$2:$C$3,Criteria!E$2:E$3,0)
The problem with this is that it wont work with the lines that only need to match with a single criteria, like the “A1 = A” that I mentioned.
For the “pear juice” part, something like this could work, but I don’t know how to have all of those working together.
=XLOOKUP(1,--ISNUMBER(SEARCH("pear",Criteria!$C$2:$C$3)),Criteria!E$2:E$3,2)
I also tried using this, but the “and” function returns 0 since not the “–ISNUMBER” returns both zeros and ones.
=XLOOKUP(1,AND(--ISNUMBER(SEARCH(Criteria!A2:A3,A14)),--INSNUMBER(SEARCH(Criteria!B2:B3,B14)),--ISNUMBER(SEARCH(Criteria!C2:C3,C14))),Criteria!E2:E3,0)
The database is also on PowerQuery, so if there is an easier way to do it there it would be great.
3
Assumptions:
A1: Value to match in Info1B1: Value to match in Info2C1: Value to match in Info3All_Combos4: A table containing the Values for each combination of Values
To match on the “partial matches” assuming they will only occur in Info3, you can use the following formula:
=TAKE(
FILTER(
All_Combos4,
(All_Combos4[Info1] = A1) *
(All_Combos4[Info2] = B1) *
ISNUMBER(
SEARCH("*" & All_Combos4[Info3] & "*", C1)
)
),
,
-4
)
Database
Partial All_Combos4 Table
Single Row Example
Here is Power Query M Code that also looks up the matching values from the All_Combos table.
In the code below, your first table (database) is named Table1. You will need to change that to reference whatever your database really is.
Also note that, since there were only three value columns, I just coded each column separately, but this could be made more dynamic depending on your actual data.
Also, this code allows multi-word phrases in both Info2 and Info3. This should be changed if Info2 will never have a multiword phrase.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Info1", type text}, {"Info2", type text}, {"Info3", type text}}),
#"Add Value1" = Table.AddColumn(#"Changed Type","Value1", (r)=>
Table.SelectRows(All_Combos, each [Info1] = r[Info1]
and List.Contains(Text.Split(r[Info2]," "),[Info2])
and List.Contains(Text.Split(r[Info3]," "),[Info3])){0}[Value1], Percentage.Type),
#"Add Value2" = Table.AddColumn(#"Add Value1","Value2", (r)=>
Table.SelectRows(All_Combos, each [Info1] = r[Info1]
and List.Contains(Text.Split(r[Info2]," "),[Info2])
and List.Contains(Text.Split(r[Info3]," "),[Info3])){0}[Value2], Percentage.Type),
#"Add Value3" = Table.AddColumn(#"Add Value2","Value3", (r)=>
Table.SelectRows(All_Combos, each [Info1] = r[Info1]
and List.Contains(Text.Split(r[Info2]," "),[Info2])
and List.Contains(Text.Split(r[Info3]," "),[Info3])){0}[Value3], Percentage.Type),
#"Add Sum Values" = Table.AddColumn(#"Add Value3","Sum", each List.Sum(List.LastN(Record.FieldValues(_),3)), Percentage.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Add Sum Values",{"Info1", "Info2", "Info3", "Sum", "Value1", "Value2", "Value3"})
in
#"Reordered Columns"
0