2 values in vlookup formula on excel
- 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 Guys,
My question is about the formula in Excel.
When I ran my workflow it will output the data in excel.
My problem is I don't know how to vlookup using 2 values.
I have 2 sheets,
The fund name sheet and data sheet.
Fund name sheet is something like this.
Fund Name | Date | Amount 1 | Amount 2 | Amount 3 | Amount4 |
Sample Fund Name 1 | 9/1/2020 | ||||
Sample Fund Name 1 | 9/2/2020 | ||||
Sample Fund Name 1 | 9/3/2020 | ||||
Sample Fund Name 1 | 9/4/2020 | ||||
Sample Fund Name 1 | 9/5/2020 |
And my data sheet is something like this.
Fund Name | Date | Amount 1 | Amount 2 | Amount 3 | Amount4 |
Sample Fund Name 1 | 9/1/2020 | 11,223 | 23,443 | 23,555 | 12,231 |
Sample Fund Name 2 | 9/1/2020 | 12,223 | 235,243 | 425,523 | 23,344 |
Sample Fund Name 3 | 9/1/2020 | 13,223 | 447,043 | 827,491 | 34,457 |
Sample Fund Name 4 | 9/1/2020 | 14,223 | 658,843 | 1,229,459 | 45,570 |
Sample Fund Name 5 | 9/1/2020 | 15,223 | 870,643 | 1,631,427 | 56,683 |
Sample Fund Name 1 | 9/2/2020 | 132,223 | 4,417,043 | 8,274,491 | 344,557 |
I need to lookup the data from Data Sheet to Fund Name using Fund Name column and Date column.
I want something like this,
Fund Name | Date | Amount 1 | Amount 2 | Amount 3 | Amount4 |
Sample Fund Name 1 | 9/1/2020 | 11,223 | 23,443 | 23,555 | 12,231 |
Sample Fund Name 1 | 9/2/2020 | 132,223 | 4,417,043 | 8,274,491 | 344,557 |
Sample Fund Name 1 | 9/3/2020 | ||||
Sample Fund Name 1 | 9/4/2020 | ||||
Sample Fund Name 1 | 9/5/2020 |
Thank you!
Solved! Go to Solution.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It sounds like you can use a Join tool, using the Fund Name and Date as join fields.
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, @ChrisTX Thanks!,
But I don't what to use alteryx tool to do this. My output file is already formatted with color, column width, and etc. I've already tried to check the preserve formatting option but still, it ruined the formatting.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
So you've already run the workflow and it created an Excel file, and you don't want to re-run the workflow?
Your solution is to manually add a VLookup in Excel? This board might not be the best place to ask about an Excel formula.
But....if you want to use a VLookup with the data stored in two separate columns .... first - in both data sets - create a single column that concatenates the two separate columns. You might need to convert your Date to text before concatenating the two fields into one. Then make sure you sort the tab that holds the source values. Then use the single field in both datasets for the VLookup formula.
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @RichardAlt — As I could understand that now you need Excel Formula for the Excel Output from Alteryx workflow:
Refer to attached the solution (I have used this formula in Cell C2 then pasted on other cells):
=IFERROR(LOOKUP(2,1/(Data!$A:$A='Update Fund'!$A2)/(Data!$B:$B='Update Fund'!$B2),Data!C:C),0)
FYI - This kind of formulas increase the calculation time.
(Disclaimer - I am not sure if users are allowed to ask such questions, other than Alteryx)
If it resolves your query please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution. Thank you.
