In the previous post, we looked into VLOOKUP formula for exact match but where single column was involved – VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7
In this, I am presenting formula which will work for any number of columns, hence it will work for 1 column, 2 columns and so on.
Let's look at below 2 tables and get the salary into first table on the basis of First Name and Last Name.
If there are only unique records, then below formula can be used
= Table2{[F Name=[First Name], Surname=[Last Name]]}[Salary]
But if records are not unique, then below formula should be used which will work for both unique and non-unique records
= try Table2{Table.PositionOf(Table2[[F Name],[Surname]],[F Name=[First Name],Surname=[Last Name]])}[Salary] otherwise null
Explanation
Table2[[F Name],[Surname]] – A table is generated from Table2 containing only 2 columns (F Name and Surname
Table.PositionOf(Table2[[F Name],[Surname]],[F Name=[First Name],Surname=[Last Name]]) – In the above generated table, we are looking for the first record for First Name and Last Name. This gives a number if match is found.
You can refer a records in table by its index number. Table.PositionOf gives that index number. Hence Table2{index number} will give that record. Hence, Table2{Table.PositionOf(Table2[[F Name],[Surname]],[F Name=[First Name],Surname=[Last Name]])} gives that record.
Now we know that from a record we can retrieve a field. We need to retrieve Salary field.
Hence, final formula becomes
Table2{Table.PositionOf(Table2[[F Name],[Surname]],[F Name=[First Name],Surname=[Last Name]])}[Salary]
Now another way is to use Table.SelectRows
Table.SelectRows(Table2, (x)=> x[F Name]=[First Name] and x[Surname]=[Last Name]){0}[Salary]
When match is not found then these formulas will give error. To suppress this, we are using try error block. Hence, final formula
= try Table2{Table.PositionOf(Table2[[F Name],[Surname]],[F Name=[First Name],Surname=[Last Name]])}[Salary] otherwise null
= try Table.SelectRows(Table2, (x)=> x[F Name]=[First Name] and x[Surname]=[Last Name]){0}[Salary] otherwise null
The file related to this tip can be downloaded from Multi Column VLOOKUP Formula – Power Query – Tips & Tricks 8