How to get specific match from a column of multiple possible matches using xlookup?

Question

Table 1 below is the before table:

Acro.	Type A	Type B
ABC
CDE
EFG

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

Acro.	Types
ABC	A
ABC	B
CDE	B
EFG	A

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

Acro.	Type A	Type B
ABC	✓	✓
CDE	X	✓
EFG	✓	X

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?

Answer 1

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

Answer 2

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.

Answer 3

Here is what I have tried, and came up with SUM() Function,

---

• Formula used in cell E3

=SUM((Table1[[Acro.]:[Acro.]]=$D3)*(Table1[[Types]:[Types]]=RIGHT(E$2)))

---

The formulas outputs as 1's & 0's hence I have used Custom Formatting for the cells to show Check Mark ✔️ & Cross Mark ❌ using Windows Emojis.

So select the cells, press CTRL + 1, this opens the Format Cells dialog, click on Number Tab under Category select Custom below Type: remove anything existing and paste the below & press OK

[Color10][=1]✔️;[Red][=0]❌;

---

You don't need an XLOOKUP() Function here since there are multiple values for one specific Acro. , hence why you were getting only the first occurrence which is a normal behavior of any LOOKUP Functions..

So that said, you can use either [ Power Query or COUNTIFS() ] as already shown above or SUM()/SUMPRODUCT() functions to perform this task.

Also, note since you have mentioned that you are using Tables, therefore refer the formula how the locking or anchoring the column references has been done. Using Structured Reference Tables (aka Tables) makes it lot easier to work and formulas are much easier to type and read.

---