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.

Solution

Use the SPL XLL of Excel

Write the formular in cell E2:

=spl("=E(?1).(E(?2).keys(M,N,O).find([A,B,C]).House)",A1:D4,I1:L9)

As shown:

Explanation:

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  

https://stackoverflow.com/questions/63912811/how-to-lookup-an-multiple-datas-and-return-respective-data-from-the-lookup-locat