2 values in vlookup formula on excel

[RichardAlt]

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!

[ChrisTX]

It sounds like you can use a Join tool, using the Fund Name and Date as join fields.

Chris

[RichardAlt]

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.

[ChrisTX]

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

[vizAlter · Accepted solution]

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.