Hi Karl,

First, I would like to clarify what exactly do you have in mind.
- Option to populate a VLookup function in a cell using EK?
- Or option to perform a VLookup on a table within an Excel sheet, without actual Vlookup function in a cell?

Regards,
Tadej

Hi Tadej,

I need to populate M-Files property B using a value from M-Files property A to find the correct row in the spreadsheet.

Let's say that column A has a list of Postal Codes and column B has the corresponding City Names.
If I type in e.g. 9600 in property A in M-Files, then Excel Reader should be able to run a VLookup in the spreadsheet to find the row that has 9600 as value in column A and then return the correct city name from column B in the same row. The city name should then be entered as value in property B in M-Files.

This example could be handled much easier by importing the fixed list of postal codes into M-Files, but if the list were not fixed and could change from day to day, then it would be relevant to have this kind of functionality to read current values from the spreadsheet.

Hope that explains the challenge?

BR, Karl

Hi Karl,

Yes, I now understand your case. And I'm pleased to inform you that this functionality is supported in the Excel reader.
The only thing not currently supported is performing this action on the source object. We have opened a work item for this with ID 893 and will contact you as soon as this feature is added.

Below is an example rule that demonstrates how to perform this action on target objects. If you have any further questions, please feel free to ask.

{
"Name": "VLookup",
"Enabled": true,
"ConfigurationMode": "Advanced",
"Trigger": "ObjectChanged",
"Source": {
"ObjType": "{53F0C8FD-0BF0-47C4-8FA6-4C2D0DADB650}",
"Class": "{7E8B0C06-2648-4DD2-BA5B-5F544E1B8622}"
},
"TargetType": "MFiles",
"FilenameWildcards": [
"*.xlsx"
],
"ExcelActions": [
{
"Name": "VLookup update",
"Enabled": true,
"ActionType": "Range",
"IsVertical": true,
"GetDataFromRange": false,
"Sheet": "Sheet1",
"StartColumn": "A",
"StartRow": 1,
"LastColumn": "D",
"ColumnNamesInFirstRow": true,
"CreateIfNotExists": false,
"MixedValueTypesInColumn": false,
"ColumnMatchTargetConfig": [
{
"ColumnStatusFieldPropertyCondition": [
{
"Field": "Property",
"Property": "PD.UniqueId"
}
]
}
],
"TargetSearchFilters": [
{
"StatusFieldConditions": [
{
"Field": "Type",
"ValueType": "Static",
"ObjectTypeValue": "{53F0C8FD-0BF0-47C4-8FA6-4C2D0DADB650}",
"ObjTypeOperator": "MFConditionTypeEqual"
}
],
"Conditions": [
{
"Property": "{CEBF9AC9-C60C-4240-9F50-723DBF3A5CA7}",
"Operator": "MFConditionTypeEqual",
"Value": {
"Mode": "Static",
"TypedValue": {
"dataType": "lookup",
"value": {
"lookup": {
"item": {
"objectClass": "CL.Vlookup"
}
}
}
}
}
},
{
"Property": "PD.UniqueId",
"Operator": "MFConditionTypeNotEqual",
"Value": {
"Mode": "SetToNULL",
"TypedValue": {
"dataType": "integer",
"value": {
"isNull": true
}
}
}
}
]
}
],
"ExcelColumnMappings": [
{
"ExcelColumn": "A",
"Property": "PD.UniqueId"
},
{
"ExcelColumn": "C",
"Property": "PD.ColumnCValue"
}
],
"LastRow": 4
}
],
"ExecutionMode": "EventHandler",
"PropertyErrorHandlingBehaviour": "Continue",
"MonitoredProperties": []
}

Regards,
Tadej

Thank you, that sound good 😀