We use vlookup functions in lots of spreadsheets to return lookups - typically product categories based on stock codes. This is messy as (1) sometimes the lookup table data does not return a result and you get #N/A, & (2) we also must populate many spreadsheets with the same data and (3) keeping everyone using the same definitions of the table is awkward.
We want some simple functions that do the look up job for us.
The basic data is like this:
Full_Code Short_Code Sub_Category Category
xyz233 xyz storage heater water heaters
sdde223 sdde fan heater room heaters
etc
So we currently use formulae like =VLOOKUP(A1,range, 2,false) to return xyz from xyz233 but this is not ideal for the above reasons.
What we want is three functions that are self contained (eg the data is embedded in the function) so we can just call function that do the lookup for us:
1. Shortcode (Full_Code) returns the Shortcode (eg Shortcode("xyz233") returns "xyz"
2. SubCategory ("xyz") returns ("storage heater")
3. Category ("storage heater") returns ("water heaters")
If in any case the argument is not in the table the function should return ("unknown").
The table could be about 1000 Full_Code rows long and we might call it many thousand of times in a spreadsheet, so performance matters ! (vlookup easily works fast enough). Its fine to break the table up into "relational" fragments - the higher level Category function will only have 10-15 entries.
We can sort data by frequency if helpful.
The functions should be stored (I recall there is a way I think) so that any new spreadsheet can just call the functions as if it was part of excel.
We can edit the function definitions for maintenance. Note I have avoided suggesting the data is in a table in a different spreadsheet. This seems to have been slow and cumbersome in the past. We can maybe do it this way if there is no choice, but rather not.
I will send the table to the winner.
Hello,
your requirements sound very familiar to me, I have a rich experience and have done a lot of similar tasks.
It would be easy and also fun for me to create a script which would do all that you described in project description. I would really enjoy in making it.
Also, I will respond quick to additional requests, if you have any after I finish the macro.
Please contact me to discuss further details.
Regards,
Ivan
Hi!
i would suggest you to use INDEX, MATCH function instead of vlookup and also IFERROR should be used to avoid #N/A. Send me chat to get sample file.
$79 USD en 3 días
5,0 (106 comentarios)
5,6
5,6
14 freelancers están ofertando un promedio de $110 USD por este trabajo
Hi,
I would like to offer you my services. I have 7 years of experience and i am very expert in excel.I can do any task on excel with a blink of an eye because i know the importance of time. Although i am a new freelancer but i am very hardworking. If you give me a chance to work on your project i will really appreciate that. I will look forward to work with you.
Thanks
Arslan Ahmed
I have excellent command on MS Excel. I have completed complex projects in Excel before as seen in my portfolio. (like - built a reservation system in Excel that involved formulas and Macros to consolidate data from various sheets and pricing calculation.)
Dear Hiring manager,
I’m skilled and experienced researcher and data entry person. I have good working experience with Excel vlookup and Google spreadsheet.
If you consider me for this project, I will try to my best. I look forward to hearing from you.
Regards
Joynul
I am a full time software engineer and have been since the age of 18. I have vast experience in multiple languages, including Visual Basics, c#, Asp.NET, C, C++, Java, NodeJs etc etc.