index match & lookup issues

[niobate60]

Hi,
ok i have some financial data that i was using Vlookup for where i have a unique id and then i was having it return a value. This works fine for example i can vlookup id 1234 and have it return the funds of 23000. My problem is when i have duplicate values in ID1. My question is what is the best way to have it return values when i have a duplicate? I have a second Id that again may not be truly unique. So how do i use the combination of the two IDs to return the funds value.

ID1 + ID2 --- returns Funds 23,000
ID1 + ID2 ---- returns funds 34,000

Id1 ID2 funds remaining
1234 ydt1 23,000 100
1259 a56B 12,000 200
1581 adb2 10,000 2000
1234 a56B 34,000 1000

I have seen that maybe Index, match may be what i need but i dont know how to set it up. Any help is most appreciated.

 

[Aladin Akyurek]

Ok, new complication. What if i didnt have the Reference number in F1? I am trying to use the Reference number in A2 and it doesn't seem to be getting me the same answer. I cant figure out why the location of the 1234 is making such a big deal.

Would you post the formula as you have it and also indicate where it is entered?

 

[niobate60]

=IFERROR(INDEX('Raw Data 2'!$C$2:$C$3000,SMALL(IF('Raw Data 2'!$A$2:$A$3000=F$4,ROW('Raw Data 2'!$C$2:$C$3000)-ROW($F$4)),ROWS(I$4:I4))),"")

so here i have my data on a Raw data page. F$4 is the equivalent of A1 in your chart. The raw data is to large to keep on the same sheet, so this is like a report sheet that only pulls the values equal to F
F G H I
4 1234 xyz 3000 Return value (35000)
1234 zyx 2500 return value (25000)
4698 bzy 2000 Return value

 

[Aladin Akyurek]

=IFERROR(INDEX('Raw Data 2'!$C$2:$C$3000,SMALL(IF('Raw Data 2'!$A$2:$A$3000=F$4,ROW('Raw Data 2'!$C$2:$C$3000)-ROW($F$4)),ROWS(I$4:I4))),"")

so here i have my data on a Raw data page. F$4 is the equivalent of A1 in your chart. The raw data is to large to keep on the same sheet, so this is like a report sheet that only pulls the values equal to F
F G H I
4 1234 xyz 3000 Return value (35000)
1234 zyx 2500 return value (25000)
4698 bzy 2000 Return value

To re-cap:

The condition/criterion is in F4, that is, F4 houses a value like 1234.

The formula is in I4...

The formula needs the ROW(...)-ROW(...)+1 bit.

So it should be:

=IFERROR(INDEX('Raw Data 2'!$C$2:$C$3000,SMALL(IF('Raw Data 2'!$A$2:$A$3000=F$4,ROW('Raw Data 2'!$A$2:$A$3000)-ROW($A$2)+1),ROWS(I$4:I4))),"")