Table 1 below is the before table:

Table 2 below is the reference table that I'll use for table 1:

Below is the end result of table 1 that I want to see:

I tried using 2 way lookup with xlookup and it's either returning error messages or it's only returning the first entry that's been found in table 2.

ABC for example, it matches both Type A & B but it would not show '✓' for Type B since the first returned match is Type A. So it only has '✓' for Type A marked.

How do I force it to search for both ABC AND Type B specifically?

CodePudding user response：

load Table2 into powerquery (data ... from table/range... [x] headers)

add column .. custom column, name the column count, use formula: = 1

click-select column Types

transform .. pivot column .. value column:count, advanced: sum

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "count", each 1),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Types]), "Types", "count", List.Sum)
in  #"Pivoted Column"

file .. close and load ...

if that gives too much data, then as your first step, filter the two columns using the arrows atop those columns, then proceed to the add column step

CodePudding user response：

Does it need to be xlookup? Here's an alternate way using IF with COUNTIFS. If the combination of Acro and Type is more than 0, then mark with a checkmark, else an x.

Cell E2 formula:

=IF(COUNTIFS($A$2:$A$5,$D2,$B$2:$B$5,"A")>0,CHAR(252),CHAR(251))

Cell F2 formula:

=IF(COUNTIFS($A$2:$A$5,$D2,$B$2:$B$5,"B")>0,CHAR(252),CHAR(251))

Font = Wingdings

I feel like there's a slightly shorter formula to use, but this isn't terrible.