Problem description

The Excel file book1.xlsx contains the following 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.

Directions

1. Write the script:

arg1 is the parameter in the above script file, which passes the Excel file’s full path in at runtime.

A1   Import four columns of data, Name, A, B, and C, from the Excel file from row 3 to row 6; @t option enables reading the first row as column headers.

A2   Import four columns of data, House, M, N, and O, from the Excel file from row 3 to row 11, and specify M, N, and O as the key fields; @t option enables reading the first row as column headers.

A3   Loop through each row of A1 to match values of A, B, and C with A2’s key field values to find the corresponding row, and return the current House value.

A4   Return A3’s searching result to the program (such as VBA) that will call this script.

2. Save the script as a file named lookupCols.dfx. Then enter the interface off a specific program (say VBA) from the Excel file through alt+F11, write a script function for calling esproc() function to execute the dfx script, and populate the content of result set to column E beginning from cell E4, whose position is defined by nStart=4 and nEnd=5. Below is the VBA script:

Sub button_Click()

    nStart = 4

    nEnd = 5

    file = Application.ActiveWorkbook.FullName

    ret = Application.Run("esproc","D:/works/shell/excel/data/lookupCols", file)

    r = UBound(ret, 1)

    c = UBound(ret, 2)

    nStart2 = nStart + r - 1

    nEnd2 = nEnd + c - 1

    Range(Cells(nStart, nEnd), Cells(nStart2, nEnd2)) = ret

End Sub 

Q & A Collection  

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