How to Look up a Multi-column Excel Code Table & Return a Specific Value from Lookup Location

Problem description

Excel data:


We are trying to look up each row of values in columns A, B and C in the table on the left in values of columns M, N, and O in the table on the right, and return the House value in the matching row to enter it into column E in the left table, as shown below:


The task requires retrieving a specific Excel data area, and searching for records in a table sequence according to key field values and returning the value of another field. In this case, the table on the left has only 3 rows, but in real-world situations, the table probably has a lot of rows. Yet, the solution introduced below applies to both.


Use the SPL XLL of Excel

Write the formular in cell E2:


As shown:



Loop through each row of form1(left) to match values of A, B, and C with form2’s key field values(M,N,O) to find the corresponding row, and return the current House value.

Q & A Collection