#129 — Multiple Column Association

#programming #beginners #tutorial #productivity

Problem description & analysis:

Here below is a data table:

Task: Compare the values of M, N and O of each row in Sheet1 with the corresponding values in Sheet2. When the row that has the same values is found, return the House value of this row, and fill in this value in column E of Sheet1.

Solution:

To achieve this, use SPL XLL and enter the following formula in cell E2:

=spl("=E(?1).keys(M,N,O).find([?2,?3,?4]).House",Sheet2!A$1:D$9,B2,C2,D2)

The result is as follows:

Then drag E2 down to every relevant row:

Convert the data of Sheet2 to a table sequence, and specify M, N and O as key column;
Loop through each row of Sheet2, use the values of M, N and O to correspond the values of key columns of Sheet2 to search for the row with the same values. When such row is found, return the House value of the row.

Download esProc Desktop for FREE and enhance your Excel workflow using SPL XLL!! 🚀✨⬇️

✨SPL download address: esProc Desktop FREE Download

✨Plugin Installation Method: SPL XLL Installation and Configuration

✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

✨YouTube FREE courses: SPL Programming