LibreOffice Calc Macro – Multiple values from vlookup result

1 min

This LibreOffice Macro Tutorial will search for data in cells like VLOOKUP function and the result will be stored in a different cells. This problem is picked up from here.

The Calc spreadsheet contains the data like this:

Multiple Vlookup
Multiple Vlookup

We will search each product_id from A2 to A4 in product_image. Each of the image contains first 3 bytes as the product id. All the matching product ID will be shown together as csv in another cells including product_id and product_name.

The Macro

'REM  *****  BASIC  *****
Sub multiple_vlookup()

' declare variables
	Dim row1, row2, row3, prod_img_str
	row3 = 6
	' loop through all the products
	for row1 = 1 to 3
		' hold the product id and name
		prod_id = ThisComponent.Sheets(0).getCellbyPosition(0,row1)
		prod_nm =  ThisComponent.Sheets(0).getCellbyPosition(1,row1)
		' loop through the image list
		for row2 = 7 to 15
			prod_img = ThisComponent.Sheets(0).getCellbyPosition(0,row2)
			' if matched product list found hold the value
			if prod_id.Value = Mid(prod_img.String,1,3) then
				prod_img_str = prod_img_str & prod_img.String & ","				
			end if
		' put the result value
		result =  ThisComponent.Sheets(0).getCellbyPosition(2,row3)
		result.Value = prod_id.Value
		result = ThisComponent.Sheets(0).getCellbyPosition(3,row3)
		result.String = prod_nm.String
		result = ThisComponent.Sheets(0).getCellbyPosition(4,row3)
		result.String = prod_img_str
		prod_img_str = ""			
		row3 = row3 + 1


End Sub

After running this macro, the result will look like this.

Multiple Vlookup-Result
Multiple Vlookup-Result

Next Step:

More Macro Tutorials


Creator of All time Linux user and open-source supporter. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x