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

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!

Sample Data.xlsx

Workflow

Accepted answers

DataNath

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:

All comments

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

Workflow.yxmd

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703