VLOOKUP migration from Ms.Excel to Calc

[Roihan]

I have a Ms.Excel file that I used for months in Windows. Since now I am using Linux, I move from Excel to Calc.
There is a VLOOKUP formula in that file that doesn’t work on Calc.

=+VLOOKUP(Excel_BuiltIn_Extract,$Sheet2.A4:C1000,2,TRUE())

It only returns #VALUE!
I’ve read about VLOOKUP in Calc and what the #VALUE error code means, tried some solutions such as changing the cell format but it is still unable to work.
What should I do to make that formula work as how it worked in Windows?

Thanks

[mikekaganski]

What is “Excel_BuiltIn_Extract”? This formula doesn’t work for me on MS Excel 2016.

[Roihan]

I’m not sure about that one, too. I received the file via email to work some administration task. I’ve browsed google to find the Excel_BuiltIn_Extract function and it’s Calc’s version but found nothing.

[Lupp]

@Roihan: I suppose you know what you want to achieve based on the formula. Doesn’t this knowledge enable you to find out by what expression or reference or whatever the first parameter must be filled semantically to reach the goal?
Though “Excel_BuiltIn_Extract” sounds somehow like a specific feature in Excel, it may also be a name chosen by someone else for a named Formula/Reference/VBAFunction that didn’t properly translate to Calc internally. To recreate it you will need to know.

[Roihan]

Sheet 1 is transaction form. Sheet 2 contains the Customers Identity. Sheet 1 uses that formula to take the value (Name, Address, and Number) from Sheet 2.
Is there a way to check that named Formula/Reference/VBAFunction?

[Roihan]

I found the named expressions. And in range or formula expression of Excel_BuiltIn_Extract , it contains $Sheet1.$AE$2
Then AE2 links to another cell and so on until AF2. While AF2 is a combo box contains the name of the customers. Usually, I choose the customer’s name using this combo box, then the cell Customer Name (The one contains the VLOOKUP formula) automatically filled by the name chosen on that combo box.

[Lupp]

Hopefully you did find a final solution based on the new insight?
If the named range is just one cell linked to a combo box, the chosen name is actually very misleading.

[Lupp]

If I understood the third comment by the OQ answering other comments on the question correctly, my first comment on the question may be the kernel of an answer.
I am not very experienced with the migration from Excel to Calc, but in a few cases I did some analysis. My conclusion in short: Excel sheets are often mainly optimized to look impressive. Design and functionality are equally often obscure and basically doubtable.
Many users, on the other hand, tend to think a known solution in Excel must be “the real one”. If not the libreofficeforum.org was killed I could point you to a convincing example for what I mean.

Edit:
I found a commented .ods I made as an attachment to the mentioned thread in the late libreofficeforum. I slightly completed the text there to get the version attached now here. I still think it is demonstrating something. ( @ charlie.it may also know how to find the mentioned thread in the archive he created.)

[Roihan]

I found it. There are another cell with #VALUE! in my sheet which also linked to the VLOOKUP I’ve mentioned above.
These are what I’ve done :
I edited =+SUM(TRIM(AF6)) to TRIM(AF6)
Then, =+VLOOKUP(Excel_BuiltIn_Extract,$Sheet2.A4:C1000,2,TRUE()) to =+VLOOKUP(Excel_BuiltIn_Extract,$Sheet2.A4:C1000,2,FALSE()).
I don’t know will it cause error to another cell but so far it works well.