Index and Match Duplicates

[matthewlouis]

I am using Index and Match with the variable being a percentage. However, I have duplicate percentages that have different matches (rows). So it is only returning the first row's information.

How do I get the Index and Match formula to recognize the different data on different rows given the duplicate percentages.

=IF(D26<>"",INDEX('INDEX Bullish RR'!$A$1:$DL$1,MATCH(D26,'INDEX Bullish RR'!$A$2:$DL$2,0)),"") where D26 = 6.35% and returns one row of data -- then the next row is . . .

=IF(D26<>"",INDEX('INDEX Bullish RR'!$A$1:$DL$1,MATCH(D27,'INDEX Bullish RR'!$A$2:$DL$2,0)),"") where D27 ALSO = 6.35% but it still returns the data from the row above it and ignores this row's data.

Thanks in advance for your imput and ideas

 

[matthewlouis]

Oh wow! Thanks Aladin, this ought to help me see better what to do. You're awesome, thanks so much!

 

[matthewlouis]

Works perfect!!!! Thank you so much, you're awesome!

 

[matthewlouis]

Hey Aladin, hmmmm, having a problem with the formula (control+alt+delete) -- I have it in column A. It worked great with the 5.06% duplicate as I said in the prior post.

But when I copied it down, it did not pick up the 5.03% you see that was duplicated at the bottom. It's just blank (?) Is there an adjustment that needs to be made to contorl+alt+delete formula? It sure worked well on that first duplicate.

<tbody>
[TD="class: xl63"][/TD]
[TD="class: xl65, width: 347"]Intermediate SP High[/TD]
[TD="class: xl66, width: 151, align: right"]1/22/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,808.12[/TD]
[TD="class: xl67, width: 151, align: right"]2,872.87[/TD]
[TD="class: xl68, width: 151, align: right"]7.72%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Hasn't Closed Over SPH[/TD]

[TD="class: xl70, width: 79"]17[/TD]
[TD="class: xl75, width: 347"]Daily SP High[/TD]
[TD="class: xl71, width: 151, align: right"]3/13/2018[/TD]
[TD="class: xl73, width: 151, align: right"]2,758.68[/TD]
[TD="class: xl73, width: 151, align: right"]2,801.90[/TD]
[TD="class: xl74, width: 151, align: right"]5.06%[/TD]
[TD="class: xl70, width: 91"]2[/TD]
[TD="class: xl69, width: 411"]Hasn't Closed Over SPH[/TD]

[TD="class: xl70, width: 79"]34[/TD]
[TD="class: xl75, width: 347"]Intermediate SP High[/TD]
[TD="class: xl71, width: 151, align: right"]3/12/2018[/TD]
[TD="class: xl73, width: 151, align: right"]2,741.47[/TD]
[TD="class: xl73, width: 151, align: right"]2,801.90[/TD]
[TD="class: xl74, width: 151, align: right"]5.06%[/TD]
[TD="class: xl70, width: 91"]2[/TD]
[TD="class: xl69, width: 411"]Hasn't Closed Over SPH[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Daily SP High[/TD]
[TD="class: xl66, width: 151, align: right"]2/27/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,744.22[/TD]
[TD="class: xl67, width: 151, align: right"]2,789.15[/TD]
[TD="class: xl68, width: 151, align: right"]4.58%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Hasn't Closed Over SPH[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Intermediate Wide Price and High Volume[/TD]
[TD="class: xl66, width: 151, align: right"]1/29/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,759.97[/TD]
[TD="class: xl67, width: 151, align: right"]2,870.62[/TD]
[TD="class: xl68, width: 151, align: right"]3.37%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Intermediate Wide Price and High Volume[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Daily SP High[/TD]
[TD="class: xl66, width: 151, align: right"]3/21/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,709.79[/TD]
[TD="class: xl67, width: 151, align: right"]2,739.14[/TD]
[TD="class: xl68, width: 151, align: right"]2.71%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Hasn't Closed Over SPH[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Intermediate Wide Price and High Volume[/TD]
[TD="class: xl66, width: 151, align: right"]11/27/2017[/TD]
[TD="class: xl67, width: 151, align: right"]2,598.87[/TD]
[TD="class: xl67, width: 151, align: right"]2,657.74[/TD]
[TD="class: xl68, width: 151, align: right"]-0.34%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Intermediate Wide Price and High Volume[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Intermediate Wide Price and High Volume[/TD]
[TD="class: xl66, width: 151, align: right"]2/26/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,647.32[/TD]
[TD="class: xl67, width: 151, align: right"]2,789.15[/TD]
[TD="class: xl68, width: 151, align: right"]-0.74%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Intermediate Wide Price and High Volume[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Daily Wide Price and High Volume[/TD]
[TD="class: xl66, width: 151, align: right"]2/9/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,532.69[/TD]
[TD="class: xl67, width: 151, align: right"]2,638.67[/TD]
[TD="class: xl68, width: 151, align: right"]-1.06%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Daily Wide Price and High Volume[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Daily Wide Price and High Volume[/TD]
[TD="class: xl66, width: 151, align: right"]2/6/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,593.07[/TD]
[TD="class: xl67, width: 151, align: right"]2,701.04[/TD]
[TD="class: xl68, width: 151, align: right"]-2.85%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Daily Wide Price and High Volume[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Daily Wide Price and High Volume[/TD]
[TD="class: xl66, width: 151, align: right"]2/8/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,580.56[/TD]
[TD="class: xl67, width: 151, align: right"]2,685.27[/TD]
[TD="class: xl68, width: 151, align: right"]-3.35%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Daily Wide Price and High Volume[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Intermediate SP Low[/TD]
[TD="class: xl66, width: 151, align: right"]11/13/2017[/TD]
[TD="class: xl67, width: 151, align: right"]2,557.45[/TD]
[TD="class: xl67, width: 151, align: right"]2,590.09[/TD]
[TD="class: xl68, width: 151, align: right"]-4.11%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Hasn't Closed Under SPL[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Daily SP Low[/TD]
[TD="class: xl66, width: 151, align: right"]4/2/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,553.80[/TD]
[TD="class: xl67, width: 151, align: right"]2,638.30[/TD]
[TD="class: xl68, width: 151, align: right"]-4.24%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Hasn't Closed Under SPL[/TD]

[TD="class: xl70, width: 79"][/TD]
[TD="class: xl71, width: 347"]Daily SP Low[/TD]
[TD="class: xl72, width: 151, align: right"]2/9/2018[/TD]
[TD="class: xl73, width: 151, align: right"]2,532.69[/TD]
[TD="class: xl73, width: 151, align: right"]2,638.67[/TD]
[TD="class: xl74, width: 151, align: right"]-5.03%[/TD]
[TD="class: xl70, width: 91"]2[/TD]
[TD="class: xl65, width: 411"]Hasn't Closed Under SPL[/TD]

[TD="class: xl70, width: 79"][/TD]
[TD="class: xl71, width: 347"]Daily SP Low[/TD]
[TD="class: xl72, width: 151, align: right"]2/9/2018[/TD]
[TD="class: xl73, width: 151, align: right"]2,532.69[/TD]
[TD="class: xl73, width: 151, align: right"]2,638.67[/TD]
[TD="class: xl74, width: 151, align: right"]-5.03%[/TD]
[TD="class: xl70, width: 91"]2[/TD]
[TD="class: xl65, width: 411"]Hasn't Closed Under SPL[/TD]

[TD="class: xl64, width: 79"][/TD]
[TD="class: xl65, width: 347"]Intermediate Wide Price and High Volume[/TD]
[TD="class: xl66, width: 151, align: right"]2/5/2018[/TD]
[TD="class: xl67, width: 151, align: right"]2,532.69[/TD]
[TD="class: xl67, width: 151, align: right"]2,763.39[/TD]
[TD="class: xl68, width: 151, align: right"]-5.30%[/TD]
[TD="class: xl64, width: 91"]1[/TD]
[TD="class: xl65, width: 411"]Intermediate Wide Price and High Volume[/TD]

</tbody>

 

[Aladin Akyurek]

Are you trying to display rows related to -5.06% and -5.03 in the same output?

 

[matthewlouis]

By same output, I am assuming you mean are they both accessing the same source data table (?) Yes, they are both indexing the same column in the source data table for the percentage (-5.06% and -5.03%) and then matching the row data. The -5.06% worked great as your formula returned (using column g) all the row data correctly for the two occurrences of -5.06%. It found Line 17 and Line 34 in my source table and applied the row data per those row numbers.

However, when I copied the control+shift+enter formula in Column A down . . . it didn't work for the -5.03% duplication as you can see.

 

[Aladin Akyurek]

Is this the output you want?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

3​

[/td][td]Daily SP High[/td][td]

3/13/2018​

[/td][td]

2,758.68​

[/td][td]

2,801.90​

[/td][td]

5.06%​

[/td][td]

2​

[/td][td]Hasn't Closed Over SPH[/td][/tr]
[tr][td]

4​

[/td][td]Intermediate SP High[/td][td]

3/12/2018​

[/td][td]

2,741.47​

[/td][td]

2,801.90​

[/td][td]

5.06%​

[/td][td]

2​

[/td][td]Hasn't Closed Over SPH[/td][/tr]
[tr][td]

5​

[/td][td]Daily SP Low[/td][td]

2/9/2018​

[/td][td]

2,532.69​

[/td][td]

2,638.67​

[/td][td]

-5.03%​

[/td][td]

2​

[/td][td]Hasn't Closed Under SPL[/td][/tr]
[tr][td]

6​

[/td][td]Daily SP Low[/td][td]

2/9/2018​

[/td][td]

2,532.69​

[/td][td]

2,638.67​

[/td][td]

-5.03%​

[/td][td]

2​

[/td][td]Hasn't Closed Under SPL[/td][/tr]
[/table]

 

[matthewlouis]

Yes, but the -5.03% has different row information as it is a duplicate . .. but yes, that is the table layout with Column A being the Row # of where the -5.03% is located in the source table.

 

[matthewlouis]

This is what it should look like -- BUT -- I had to manipulate the Column A formula to get it to work -- I couldn't just copy it down from Cell A2

<tbody>
[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Intermediate SP High[/TD]
[TD="class: xl101, width: 235"]1/22/2018[/TD]
[TD="class: xl98, width: 136, align: right"]2,808.12[/TD]
[TD="class: xl98, width: 135, align: right"]2,872.87[/TD]
[TD="class: xl102, width: 186, align: right"]7.47%[/TD]
[TD="class: xl109, width: 94"]1[/TD]
[TD="class: xl99, width: 204"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Over SPH[/TD]

[TD="class: xl110"]17[/TD]
[TD="class: xl105"]Daily SP High[/TD]
[TD="class: xl107, align: right"]3/13/2018[/TD]
[TD="class: xl108, align: right"]2,758.68[/TD]
[TD="class: xl108, align: right"]2,801.90[/TD]
[TD="class: xl102, align: right"]4.82%[/TD]
[TD="class: xl109"]2[/TD]
[TD="class: xl99"][/TD]
[TD="class: xl106"]Hasn't Closed Over SPH[/TD]

[TD="class: xl110"]34[/TD]
[TD="class: xl105"]Intermediate SP High[/TD]
[TD="class: xl107, align: right"]3/12/2018[/TD]
[TD="class: xl108, align: right"]2,741.47[/TD]
[TD="class: xl108, align: right"]2,801.90[/TD]
[TD="class: xl102, align: right"]4.82%[/TD]
[TD="class: xl109"]2[/TD]
[TD="class: xl99"][/TD]
[TD="class: xl106"]Hasn't Closed Over SPH[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Daily SP High[/TD]
[TD="class: xl101"]2/27/2018[/TD]
[TD="class: xl98, align: right"]2,744.22[/TD]
[TD="class: xl98, align: right"]2,789.15[/TD]
[TD="class: xl102, align: right"]4.34%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl99"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Over SPH[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Intermediate Wide Price and High Volume[/TD]
[TD="class: xl101"]1/29/2018[/TD]
[TD="class: xl98, align: right"]2,759.97[/TD]
[TD="class: xl98, align: right"]2,870.62[/TD]
[TD="class: xl102, align: right"]3.15%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl99"]0[/TD]
[TD="class: xl104, width: 711"]Intermediate Wide Price and High Volume[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Daily SP High[/TD]
[TD="class: xl101"]3/21/2018[/TD]
[TD="class: xl98, align: right"]2,709.79[/TD]
[TD="class: xl98, align: right"]2,739.14[/TD]
[TD="class: xl102, align: right"]2.47%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl99"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Over SPH[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Daily SP High[/TD]
[TD="class: xl101"]4/18/2018[/TD]
[TD="class: xl98, align: right"]2,703.63[/TD]
[TD="class: xl98, align: right"]2,717.49[/TD]
[TD="class: xl102, align: right"]1.66%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl99"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Over SPH[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Intermediate Wide Price and High Volume[/TD]
[TD="class: xl101"]11/27/2017[/TD]
[TD="class: xl98, align: right"]2,598.87[/TD]
[TD="class: xl98, align: right"]2,657.74[/TD]
[TD="class: xl102, align: right"]-0.57%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl99"]0[/TD]
[TD="class: xl104, width: 711"]Intermediate Wide Price and High Volume[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Intermediate Wide Price and High Volume[/TD]
[TD="class: xl101"]2/26/2018[/TD]
[TD="class: xl98, align: right"]2,647.32[/TD]
[TD="class: xl98, align: right"]2,789.15[/TD]
[TD="class: xl102, align: right"]-0.97%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl100"]0[/TD]
[TD="class: xl104, width: 711"]Intermediate Wide Price and High Volume[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Daily Wide Price and High Volume[/TD]
[TD="class: xl101"]2/9/2018[/TD]
[TD="class: xl98, align: right"]2,532.69[/TD]
[TD="class: xl98, align: right"]2,638.67[/TD]
[TD="class: xl102, align: right"]-1.29%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl100"]0[/TD]
[TD="class: xl104, width: 711"]Daily Wide Price and High Volume[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Daily Wide Price and High Volume[/TD]
[TD="class: xl101"]2/6/2018[/TD]
[TD="class: xl98, align: right"]2,593.07[/TD]
[TD="class: xl98, align: right"]2,701.04[/TD]
[TD="class: xl102, align: right"]-3.09%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl100"]0[/TD]
[TD="class: xl104, width: 711"]Daily Wide Price and High Volume[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Daily Wide Price and High Volume[/TD]
[TD="class: xl101"]2/8/2018[/TD]
[TD="class: xl98, align: right"]2,580.56[/TD]
[TD="class: xl98, align: right"]2,685.27[/TD]
[TD="class: xl102, align: right"]-3.59%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl100"]0[/TD]
[TD="class: xl104, width: 711"]Daily Wide Price and High Volume[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Intermediate SP Low[/TD]
[TD="class: xl101"]11/13/2017[/TD]
[TD="class: xl98, align: right"]2,557.45[/TD]
[TD="class: xl98, align: right"]2,590.09[/TD]
[TD="class: xl102, align: right"]-4.33%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl100"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Under SPL[/TD]

[TD="class: xl110"][/TD]
[TD="class: xl103, width: 417"]Daily SP Low[/TD]
[TD="class: xl101"]4/2/2018[/TD]
[TD="class: xl98, align: right"]2,553.80[/TD]
[TD="class: xl98, align: right"]2,638.30[/TD]
[TD="class: xl102, align: right"]-4.46%[/TD]
[TD="class: xl109"]1[/TD]
[TD="class: xl100"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Under SPL[/TD]

[TD="class: xl110"]28[/TD]
[TD="class: xl105"]Daily SP Low[/TD]
[TD="class: xl107, align: right"]2/9/2018[/TD]
[TD="class: xl108, align: right"]2,532.69[/TD]
[TD="class: xl108, align: right"]2,638.67[/TD]
[TD="class: xl102, align: right"]-5.25%[/TD]
[TD="class: xl109"]2[/TD]
[TD="class: xl100"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Under SPL[/TD]

[TD="class: xl110"]36[/TD]
[TD="class: xl105"]Intermediate SP Low[/TD]
[TD="class: xl107, align: right"]2/5/2018[/TD]
[TD="class: xl108, align: right"]2,532.69[/TD]
[TD="class: xl108, align: right"]2,763.39[/TD]
[TD="class: xl102, align: right"]-5.25%[/TD]
[TD="class: xl109"]2[/TD]
[TD="class: xl100"][/TD]
[TD="class: xl104, width: 711"]Hasn't Closed Under SPL[/TD]

</tbody>

 

[Aladin Akyurek]

I'm asking for the output and getting back it seems the hole input.

Please try to put your question in terms of input and output. You shout say this is the input and post the input. And then say this is the output I want from that input and post that output.

 

[matthewlouis]

OK, I think I understand now.

Using the table last posted above, the INPUT is the percentage in Column F (that's what the formula is using to search the output for). The output I want is Columns B, C, D, E and I (last column). Again, I used your formula above and it worked on the first duplicate perfectly and filled in Columns B, C, D, E, and I -- but when I copied it down in Column A, it returned blanks in Column A, thus not creating any output in Columns, B, C, D, E, and I.

Thanks for your patience with me on this. I hope I got the input and putput right now.