Recreate Vlookup Using Join Tool

[jmmart08]

Hi All I am trying to incorporate another input to my attached workflow and use the join tool to recreate the Vlookup on the vlookup formula sheet (example =VLOOKUP($V3,'P:\1.3_operations_critical\Collateral Management\Inventory\Weekly Inventory\[Inventory Categorization.xlsx]Sheet3'!$D:$L,6,FALSE) within my example sheet by using the lookup table sheet to find matches, build logic based on this, and also incorporate anything that isn't joined as I need all the data. Also, apologies but when I click browse nothing happens and I can't drag and drop files. Anyone know if there is an issue? I've never had an issue attaching files but I've tried multiple times today and I just cannot. I know what I have here is incomplete because I can't attach anything. Any guidance would be great appreciated!! Lookup Table Issuer ID Column J Issue Minor Column P Remaining Maturity Col V Unique ID JPM WFB UST000 U.S. Government 0 UST000U.S. Government0 99.00% 98.00% UST000 U.S. Government 0 UST000U.S. Government0 99.00% 98.00% UST000 U.S. Government 1 UST000U.S. Government1 97.00% 98.00% UST000 U.S. Government 2 UST000U.S. Government2 97.00% 98.00% UST000 U.S. Government 3 UST000U.S. Government3 97.00% 98.00% UST000 U.S. Government 4 UST000U.S. Government4 97.00% 98.00% UST000 U.S. Government 5 UST000U.S. Government5 96.00% 96.00% UST000 U.S. Government 6 UST000U.S. Government6 96.00% 96.00% UST000 U.S. Government 7 UST000U.S. Government7 96.00% 96.00% UST000 U.S. Government 8 UST000U.S. Government8 96.00% 96.00% UST000 U.S. Government 9 UST000U.S. Government9 96.00% 96.00% UST000 U.S. Government 10 UST000U.S. Government10 91.00% 96.00% UST000 U.S. Government 11 UST000U.S. Government11 91.00% 96.00% UST000 U.S. Government 12 UST000U.S. Government12 91.00% 96.00% UST000 U.S. Government 13 UST000U.S. Government13 91.00% 96.00% UST000 U.S. Government 14 UST000U.S. Government14 91.00% 96.00% UST000 U.S. Government 15 UST000U.S. Government15 91.00% 96.00% UST000 U.S. Government 16 UST000U.S. Government16 91.00% 96.00% UST000 U.S. Government 17 UST000U.S. Government17 91.00% 96.00% UST000 U.S. Government 18 UST000U.S. Government18 91.00% 96.00% UST000 U.S. Government 19 UST000U.S. Government19 91.00% 96.00% UST000 U.S. Government 20 UST000U.S. Government20 91.00% 96.00% UST000 U.S. Government 21 UST000U.S. Government21 91.00% 96.00% UST000 U.S. Government 22 UST000U.S. Government22 91.00% 96.00% UST000 U.S. Government 23 UST000U.S. Government23 91.00% 96.00% UST000 U.S. Government 24 UST000U.S. Government24 91.00% 96.00% UST000 U.S. Government 25 UST000U.S. Government25 91.00% 96.00% UST000 U.S. Government 26 UST000U.S. Government26 91.00% 96.00% UST000 U.S. Government 27 UST000U.S. Government27 91.00% 96.00% UST000 U.S. Government 28 UST000U.S. Government28 91.00% 96.00% UST000 U.S. Government 29 UST000U.S. Government29 91.00% 96.00% UST000 U.S. Government 30 UST000U.S. Government30 91.00% 96.00% FHLMC0 U.S. Government NA FHLMC0U.S. GovernmentNA 91.00% 92.00% FNMA00 U.S. Government NA FNMA00U.S. GovernmentNA 91.00% 92.00% GNMA00 U.S. Government NA GNMA00U.S. GovernmentNA 91.00% 92.00% GNMAII U.S. Government NA GNMAIIU.S. GovernmentNA 91.00% 92.00% 628962 U.S. Government NA 628962U.S. GovernmentNA Not Eligible Not Eligible GSE999 ABS NA GSE999ABSNA Not Eligible Not Eligible GSE999 CMO NA GSE999CMONA Not Eligible Not Eligible GSE999 Commercial Paper NA GSE999Commercial PaperNA Not Eligible Not Eligible GSE999 Corporate Bonds NA GSE999Corporate BondsNA Not Eligible Not Eligible GSE999 Lotteries NA GSE999LotteriesNA Not Eligible Not Eligible GSE999 Munis NA GSE999MunisNA Not Eligible Not Eligible GSE999 Repo NA GSE999RepoNA Not Eligible Not Eligible

[jrgo]

@jmmart08 

Use a Union or Find Replace tool... 

[jmmart08]

Hi @jrgo 

I was able to use Chrome to attach my inputs and workflow.  Any further guidance you might be able to give me with the actual inputs?  Let me know if my original post makes sense.

Thank you very much.

[jrgo · Accepted solution]

Here you go... as illustrated in my last post, use a Union tool to keep all the UNJOINED (unmatched) data from one side with the joined/matched data (from the J output). in this case, your main data was connected to the L input of the JOIN and I connected both the L and J output of the Join into the Union.

On a side note, unlike a VLOOKUP which requires that you concatenate all the values to create your lookup value, with a JOIN tool, you don't need to do that and can just select each field to establish the join (or lookup) on.

[jmmart08]

Hi @jrgo 

This is GREAT!!  Thank you very much.  I had 1 final question.  I have to incorporate 1 last input into my process.  I have attached this.  There is only 1 item on here but in the future there could be several items (not a set amount) that I need to add to my join and union.  I have attached the file.  I need to add 3 columns to the end of my output from this file (Amount already on deposit, reason for deposit, and available units.  The amount already on deposit and reason for deposit columns are once again using a Vlookup in the excel sheets.  It is looking for the Unique ID column on my original Example excel sheet and then referencing a vlookup on the file I have now attached and looking at columns to return.  I'm not sure where to incorporate this into the join or union.  If you can provide some guidance that would be amazing.  I really appreciate your assistance up to this point.  This is tremendous!!

[jmmart08]

Hi @jrgo 

Sorry to reach out again.  I am trying to join 1 more excel file file in my entire workflow and can't seem to figure it out.  The attached may have data that is also on my original input example spreadsheet and I need those to figure in to my join/union.  I can write the logic necessary to add some additional columns but when I try to join this what is happening is I'm duplicating the security ID and primary sec ID.  I want them to mesh and have them part of my over all flow as 1 line item with the current logic I have built in.  Any assistance/guidance to send me down the right path would be very much appreciated.

Thanks so much!

[jrgo]

Hi @jmmart08 

I’ll take a look at this tomorrow (already away from PC). Can you share an example of what you’re expecting to see after the file you shared is joined in?

[jmmart08]

Hi @jrgo 

Thank you.  Please see the attached workflow.  I am just trying to incorporate 1 more input (Query on Deposit Inventory GNWY 03.21.21) into the workflow you already helped me with.  I need to matchup any values in the PRIMARYSECID column on this sheet with the same SECURITY_ID on my example sheet but just add 3 more columns.  What I can't figure out is how to join into 1 line item with all of the same logic in my original, plus the formulas I created for this new file.  What I get now is 2 separate line items as seen below for SECURITY_ID 912810ET1 as this is the value that is on both sheets.  Again I am just trying to combine this into 1 line item with all of my formulas applying to all columns plus adding the 3 additional columns (Amount already on deposit, reason for deposit, available units) to the end of my original workflow.  Hopefully this makes sense and thank you so much for your help!

AS_OF_DATE

SAP

PORTFOLIO

SECURITY_ID

GL_GROUP_CODE

ISSUER_IDENTIFIER

ISSUER_NAME

ISSUER_MAJOR_GROUP

ISSUER_MINOR_GROUP

ISSUE_NAME

ISSUE_MAJOR_GROUP

ISSUE_MINOR_GROUP

COUPON_RATE_BASE

MATURITY_DATE_BASE

SumOfAS_OF_PAR_SHARES

SumOfAS_OF_MARKET_VALUE

Unique ID

Held as of Previous File?

Hedge Port?

Remaining Maturity

Maturity Truncated or NA

Unique Collateral ID

JPM

WFB

Issuer ID Column J

Issue Minor Column P

Remaining Maturity Col V

Right_Unique ID

Right_JPM

Right_WFB

F7

PRIMARYSECID

AMOUNT

REASON

Amount already on deposit

Reason for Deposit

Available Units

3/31/2021

1330

21099

912810ET1

20

UST000

UNITED STATES OF AMERICA

U.S. GOVERNMENTS

U.S. GOVERNMENTS

TREASURY BONDS

Fixed Income - Non MBS

U.S. Government

1

2/15/2025

1000000

1250595.45

21099912810ET1

3.88

3

UST000U.S. Government3

UST000

U.S. Government

3

UST000U.S. Government3

0.97

0.98

3/31/2021

1330

21099

912810ET1

20

UST000

UNITED STATES OF AMERICA

U.S. GOVERNMENTS

U.S. GOVERNMENTS

TREASURY BONDS

Fixed Income - Non MBS

U.S. Government

1

2/15/2025

1000000

1250595.45

912810ET1

1500000

All Policyholders

1500000

All Policyholders

-500000

[jrgo]

@jmmart08 

If I understood correctly, attached would be the proper setup.

[jmmart08]

Hi @jrgo 

Is there a way to basically just collapse that security 912810ET1?  I want it to be part of my overall output not just that security as isolated.  Just can't figure out where to add in joins and/or unions or another tool to incorporate this to the overall output.  In the last join on the workflow you provided, I still have this ID 912810ET1 as part of my left and joined outputs.  Looking to see if I can incorporate all of the logic, additional columns, etc into 1 line item and still output all of my data.  Let me know if that makes any sense.  As the setup is now, my join just isolates the 1 security and it outputs what I expected for that 1 ID but that ID also still exists in my left output as well as the join output.  Hopefully this helps if.  Thanks again for taking the time to help.