Hello! I'm trying to do somthing similar to what an index match (or a vlookup with helper columns) would do.
{=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}
Excel formula: VLOOKUP with multiple criteria | Exceljet
I want to add some details to the main transaction dataset from another data set. This 2nd dataset has more granular detail so the fields I'm want to add to the main dataset will repeat within the same transaction (i.e. the 2nd dataset is by invoice/so line, the main dataset does not have these). I have tried to do this with the join or join multiple but it adds a a bunch of extra rows (due to the detials being by invoice/so line on the 2nd dataset, I think). I would use the find replace but it does not allow additional criterias.
This is the main report portion I'm trying to add detials to.
These are the matched transactions.
Thanks!
@jobregon if I am understanding correctly, you should be able to replicate the VLOOKUP behavior (of returning only the first value) by using a Sample tool after the join. I have attached an example workflow which samples the first record (N=1) grouping by Field 1. You could modify this logic to replicate it with your data set.
User | Count |
---|---|
19 | |
14 | |
13 | |
9 | |
8 |