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 NameDateAmount 1Amount 2Amount 3Amount4Sample Fund Name 19/1/2020 Sample Fund Name 19/2/2020 Sample Fund Name 19/3/2020 Sample Fund Name 19/4/2020 Sample Fund Name 19/5/2020 And my data sheet is something like this. Fund NameDateAmount 1Amount 2Amount 3Amount4Sample Fund Name 19/1/202011,22323,44323,55512,231Sample Fund Name 29/1/202012,223235,243425,52323,344Sample Fund Name 39/1/202013,223447,043827,49134,457Sample Fund Name 49/1/202014,223658,8431,229,45945,570Sample Fund Name 59/1/202015,223870,6431,631,42756,683Sample Fund Name 19/2/2020132,2234,417,0438,274,491344,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 NameDateAmount 1Amount 2Amount 3Amount4Sample Fund Name 19/1/202011,22323,44323,55512,231Sample Fund Name 19/2/2020132,2234,417,0438,274,491344,557Sample Fund Name 19/3/2020 Sample Fund Name 19/4/2020 Sample Fund Name 19/5/2020 Thank you!

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!

Output

Accepted answers

vizAlter

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.

Lookup based on two columns in Excel.xlsx

All comments

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.

vizAlter

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.

Lookup based on two columns in Excel.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703