Vlookup an amount from multiple sheets in an excel based on company name

[jdkatz1]

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!

[IraWatt]

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

[jdkatz1]

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

[DataNath · Accepted solution]

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:

[DawnDuong]

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.

[jdkatz1]

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?

[jdkatz1]

How do i connect this to the main workflow? this is one input/section of a larger process flow that has 4 other inputs.