Vlookup an amount from multiple sheets in an excel based on company name
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I am trying to bring in the cost amount from multiple tabs within an excel based on the company name and join the data with 2 other input files in my workflow. I have looked at a lot of Macros and forum posts, and I still cant figure out how to implement anything for this specifically.
.
Attached is a sample excel, it has two tabs with the format and sample data we get from a client.
Thanks in advance!
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @jdkatz1,
A VLOOKUP is very similar to a join so I just dragged both sheets on to the canvas and joined on there company name:
Any questions or issues please ask :)
HTH!
Ira
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The real excel has 18 tabs, i just used two for an example. Any way to modify the workflow to address that? Additionally, is there any way to keep everything in the same columns and the info will just be rows following the same naming convention? this will help when i join to the rest of the workpapers to lookup the cost value based on the company name
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @jdkatz1 if you have a load like you say then you can use the Join Multiple tool which will act the same as the join, but (as the grey input anchor indicates), allows multiple connections - I'm yet to find the limit so it's extensive. This will place them side by side as shown below (I've just shown 3 as an example that it allows > 2):
If your sheets follow the same schema and naming convention and you just want to stack them as mentioned, you'd use a Union tool for this, selecting 'Auto Config by Name' in the configuration:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @jdkatz1
When you have multiple excel "fund costs" tabs that you want to vlookup to, it is more efficient if you stack all the tabs e.g. Fund1, Fund2 etc... first and then use the Join tool to implement the equivalent of Excel vlookup. This "stacking" approach allows you to read any number of tabs i.e. you don't even need to know how many tabs are contained in the Excel files to be able to read all of them into a single consolidated file.
If all the "Fund x" tabs have identical data structure, it can be done quite cleanly with the Dynamic Input tool (Under the Developer category). Refer to this Knowledge Base article for a very comprehensive guide on how to import multiple Excel sheets: How To: Import Multiple Excel Sheets or a Specific... - Alteryx Community
Regards,
Dawn.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This was helpful. stupid question, how do you connect this to the main workflow? it ends with the browse, do you connect it via the dynamic input?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
How do i connect this to the main workflow? this is one input/section of a larger process flow that has 4 other inputs.
