VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7

Generally, a merge is performed and then various succeeding operations are performed to arrive at Exact Match for VLOOKUP/XLOOKUP. But all these steps can be replaced with a single formula in Power Query which is faster than merge operation. Read on…..

Let's recap VLOOKUP formula for Exact Match.

=VLOOKUP(A2,Sheet2!A:C,3,0)

=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C)

This will look for exact match for A2 in column A of Sheet2 and will return the matched value for column C of Sheet2.

1. If there are more than one match, it will return first matched value.
2. If match is not found, it will return #N/A error.
3. To avoid #N/A error, the above formula can be enclosed in IFERROR block to produce a blank output in case of #N/A error.

=IFERROR(VLOOKUP(A2,Sheet2!A:C,3,0),"")

=IFERROR(XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C),"")

Now, refer to following where we need to produce OUTPUT TABLE.

The classical way is to use following VLOOKUP formula

= LookupTbl{[Prod Name=[Product Name]]}[Supplier]

But for the cases where more than one match is there, it will give error.

To remove this limitation, we can make use of Table.PositionOf which can return first match.

=LookupTbl{Table.PositionOf(LookupTbl[[Prod Name]],[Prod Name=[Product Name]])}[Supplier]

Another variation of this is to use List.PositionOf to return first match (This formula length is shorter, hence I would prefer this)

=LookupTbl[Supplier]{List.PositionOf(LookupTbl[Prod Name],[Product Name])}

Another method is Table.SelectRows

=Table.SelectRows(LookupTbl,(x)=> x[Prod Name]=[Product Name]){0}[Supplier]

Now, but these formulas will give error in case of Milk as match will not be found. Hence, final formulas become

=try LookupTbl{Table.PositionOf(LookupTbl[[Prod Name]],[Prod Name=[Product Name]])}[Supplier] otherwise null

= try LookupTbl[Supplier]{List.PositionOf(LookupTbl[Prod Name],[Product Name])} otherwise null

=try Table.SelectRows(LookupTbl,(x)=> x[Prod Name]=[Product Name]){0}[Supplier] otherwise null

Note: If you want to show #N/A in place of null like VLOOKUP does, you can replace null with "N/A"

The workbook containing PQ can be downloaded from VLOOKUP Formula for Exact Match – Power Query – Tips & Tricks 7