index match & lookup issues

N

niobate60

New Member
Joined
Jun 19, 2013
Messages
7
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
niobate60 said:
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.
Click to expand...

Is the second set a data sample from which you want to obtain the values that correspond to 1234? By the way, those values can be returned either one by one or summed into a single figure.
 
Upvote 0
No the second ID2 is not necessary. I added it to the data set under the assumption that i would need or could use some combination of values to return the funding values. I want to return in two cells the 23,000 and the 34,000 and the Vlookup I have used only returns the first number and not the duplicate.
 
Upvote 0
niobate60 said:
No the second ID2 is not necessary. I added it to the data set under the assumption that i would need or could use some combination of values to return the funding values. I want to return in two cells the 23,000 and the 34,000 and the Vlookup I have used only returns the first number and not the duplicate.
Click to expand...

[TABLE="width: 288"]
<colgroup><col style="width: 48pt;" span="6" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1234[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]ydt1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23000[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Values[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1259[/TD]
[TD="class: xl65, bgcolor: transparent"]a56B[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12000[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]200[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1581[/TD]
[TD="class: xl65, bgcolor: transparent"]adb2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10000[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1234[/TD]
[TD="class: xl65, bgcolor: transparent"]a56B[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34000[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


F1: 1234

F3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX($C$2:$C$5,SMALL(IF($A$2:$A$5=F$1,
  ROW($A$2:$A$5)-ROW($A$2)+1),ROWS(F$3:F3))),"")
 
Upvote 0
Aladin Akyurek said:
[TABLE="width: 288"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1234
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1234
[/TD]
[TD="class: xl65, bgcolor: transparent"]ydt1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Values
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1259
[/TD]
[TD="class: xl65, bgcolor: transparent"]a56B
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]23000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1581
[/TD]
[TD="class: xl65, bgcolor: transparent"]adb2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2000
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1234
[/TD]
[TD="class: xl65, bgcolor: transparent"]a56B
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]34000
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1000
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


F1: 1234

F3, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX($C$2:$C$5,SMALL(IF($A$2:$A$5=F$1,
  ROW($A$2:$A$5)-ROW($A$2)+1),ROWS(F$3:F3))),"")
Click to expand...

i dont get the F1 F3 reference is for a table name that i dont know how to make

F1 is the help menu.
=IFERROR(INDEX($C$2:$C$3000,SMALL(IF($A$2:$A$3000=F$1,ROW($C$2:$C$3000)-ROW($F$6)+1),ROWS(G$7:G7))),"") my actual has 3000 rows

=IFERROR(INDEX($C$2:$C$5,SMALL(IF($A$2:$A$5=F$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS(F$3:F3))),"")

Ok complication, if 1234 is not the first data but is burried in 3000 rows, your equation gives me the First value and then the first occurance of 1234.


So in your data table example if the first occurance of 1234 was actually say 1952 rows down (A1952) and the second 1234 was A1956,how would i return those cost values vertically?
 
Upvote 0
Yes, i did the control shift enter and it puts a little {=IFERROR(INDEX($C$2:$C$3000,SMALL(IF($A$2:$A$3000=F$1,ROW($C$2:$C$3000)-ROW($F$1)+1),ROWS(G$8:G9))),"")} around it.

it gave me the value right below the duplicate. so i took out the +1.

{=IFERROR(INDEX($C$2:$C$3000,SMALL(IF($A$2:$A$3000=F$1,ROW($C$2:$C$3000)-ROW($F$1)),ROWS(G$8:G9))),"")}

This returned the Second value (ie, the 34,000) that VLook UP has always missed.

It does not produce the results in G8 and G9 as you indicate in your F$3:F3. it is so close, but not quite there. what else can we try?
 
Upvote 0
niobate60 said:
Yes, i did the control shift enter and it puts a little {=IFERROR(INDEX($C$2:$C$3000,SMALL(IF($A$2:$A$3000=F$1,ROW($C$2:$C$3000)-ROW($F$1)+1),ROWS(G$8:G9))),"")} around it.

it gave me the value right below the duplicate. so i took out the +1.

{=IFERROR(INDEX($C$2:$C$3000,SMALL(IF($A$2:$A$3000=F$1,ROW($C$2:$C$3000)-ROW($F$1)),ROWS(G$8:G9))),"")}

This returned the Second value (ie, the 34,000) that VLook UP has always missed.

It does not produce the results in G8 and G9 as you indicate in your F$3:F3. it is so close, but not quite there. what else can we try?
Click to expand...

F1 houses 1234.

F3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$3000,SMALL(IF($A$2:$A$3000=F$1,ROW($C$2:$C$3000)-ROW($C$2)+1),ROWS(F$3:F3))),"")
 
Upvote 0
Wow,

Thank you, it worked. Now i will have to play with it to understand why it works, and apply it to my larger sheet. Thank you very much. I am sure though this wont be the last issue.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Index/Match Duplicate Values With No Helper Cells
Replies
3
Views
298
Domenic
D
T
  • Solved
Index match multiple columns and true/false statement
2
Replies
12
Views
1K
Eric W
Eric W
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
286
etaf
etaf
K
  • Question Question
How to find stock data that Excel can't match to it's data source provider?
Replies
0
Views
497
kcmuppet
K
L
  • Solved
Index and Match
Replies
5
Views
378
Lizzi
L

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top