VLOOKUP to pull in duplicate values

[Aredeekay]

Good day,

Currently I have a formula to pull in fees from a different TAB but now and then I run across the issue when there are duplicate values :

Main formula on first sheet :

=IF($A2="","",VLOOKUP($C2,'LenderDailyBilling-MAML (6)'!$A$4:$O$31,15,0)*$D2)

The tab where the data is being pulled from :

So in this instance I have 2 x GFI rows which values i need and it simply ignores the second, how can i amend my formula to have both pulled in?

Ticker Id
-----------------------------------
SNH
GFI
GFI
ANG
BTI
VOD
PSG
TKG


Regards

[AliGW]

You cannot do what you want with VLOOKUP, which returns ONLY the first match.

Look at the yellow banner up top to find out how to attach your sample workbook - don't forget to manually mock-up your expected results.

[Aredeekay]

Thank you - file attached.

Would you suggest a different formula/approach achieve the desired result?

[AliGW]

Well, I guess so - did you read what I said?

I am going to have a look now.

[AliGW]

OK - the problem you have in the lookup sheet is that there is nothing that distinguishes the first GFI entry from the second - how is Excel meant to know which one to use for each line in the results column? What is the logic? Is it just a case of the next one down?

[Aredeekay]

Yes it's essentially the next one down. All the values from the LenderDailyBilling tab needs to be used and split accordingly

[NARAYANK991]

Hi ,

See if this works.

The formula in cell G3 is :

Please Login or Register  to view this content.

This is an array formula to be entered using CTRL SHIFT ENTER.

See the attached file.

Narayan

[AliGW]

OK - so two questions:

1. Are the result and lookup datasets in the same order? If not, then we shall need a different approach.
2. What happens if there are 3 GFIs (or any other ID) in the results set, but only 2 in the lookup set?

[Aredeekay]

This seems to pull in the duplicates but returns the incorrect values

[Aredeekay]

1. It's in a different order.
2. The lookup & result set will always have the same amount of values, it needs to match.

To give a short summary, the lookup set has the Fees & portfolios %'s whereas the result set takes the fees and splits them according to the portfolio %

[AliGW]

OK - thanks for clarifying.

[Aredeekay]

Anyone perhaps have a solution for this?

[Glenn Kennedy]

I am a little confused. Pulling back two values for GFI is easy.... and I understand why. But. Why are you expecting two results for ANG, when there is only one entry for ANG on "LenderDailyBilling-MAML (6)"

[Aredeekay]

It changes every day, for example on this day there was only 1 duplicate, the 2 GFI'S, but the next could be multiple duplicates

[Glenn Kennedy]

So.... do you want a blank for the second ANG... or only to get the correct number of ANGs on each day?

[Glenn Kennedy]

Take alook:

=IFERROR(INDEX('LenderDailyBilling-MAML (6)'!O:O,AGGREGATE(15,6,ROW('LenderDailyBilling-MAML (6)'!$O$4:$O$100)/('LenderDailyBilling-MAML (6)'!$A$4:$A$100=C3),COUNTIF($C$3:C3,C3)))*$D3,"")


If this is incorrect, what do you expect to see.... and why?

[Aredeekay]

Take alook:

=IFERROR(INDEX('LenderDailyBilling-MAML (6)'!O:O,AGGREGATE(15,6,ROW('LenderDailyBilling-MAML (6)'!$O$4:$O$100)/('LenderDailyBilling-MAML (6)'!$A$4:$A$100=C3),COUNTIF($C$3:C3,C3)))*$D3,"")


If this is incorrect, what do you expect to see.... and why?

Hi Glenn,

I think i had to go a bit more in detail on what the sheet does exactly and the expected results that I'm looking for :

To summarize :

On the first sheet the formulas pull in the fees for each stock as per the 2nd sheet(LenderDailyBilling-MAML) and splits them according to the portfolio %'s as per the 3rd sheet(SecLendingDetail)

For example :

R100 in fees for ANG , ANG is split into 2 portfolios(39% to to and 61% for the other) and then split the fees 39$ to 1 and 61$ to the other according to the % split.

In this instance GFI appears twice on the fee sheet, and needs to be split twice, whereas my original formula only takes the first line of GFI and splits it accordingly, I'm looking for a way to amend the formula to take each GFI(or whichever stock it will be on the given day) and split each of them.

For example on the 2nd sheet GFI has 2 values : 103.23 & 28.74

According to the 3rd sheet GFI is split into 2 portfolios - MMITED 36% & MMISXD 64%

Now the current formula only takes the first GFI value of 103.23 and splits it between the portfolios but ignores the 2nd value(28.74)

Apologies for the confusion

[Glenn Kennedy]

Now I understand!!

=IF($A3="","",SUMIF('LenderDailyBilling-MAML (6)'!A:A,'Fee split Managed per day'!C3,'LenderDailyBilling-MAML (6)'!O:O)*$D3)

[Aredeekay]

Now I understand!!

=IF($A3="","",SUMIF('LenderDailyBilling-MAML (6)'!A:A,'Fee split Managed per day'!C3,'LenderDailyBilling-MAML (6)'!O:O)*$D3)

Thank you so much! This works.