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

 

[Aladin Akyurek]

These formulas both look at the same row A2:DL2. If there is a match, they return a result from A1:DL1.

 

[matthewlouis]

Oops, sorry. These are the formulas.

=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$68:$DL$68,MATCH(D27,'INDEX Bullish RR'!$A$69:$DL$69,0)),"") where D27 ALSO = 6.35% but it still returns the data from the row above it and ignores this row's data.

It's looking at two different different tables but the "match" is the same number so it is only returning the first set od data and not the second.

Thanks, Aladin, what do you think?

 

[Aladin Akyurek]

Suppose we have in A1:C1

jon, matthew, jaap

In A2:C2

50,45,60

Looking for 45 in A2:C2, we will get: matthew


Suppose we have in A10:C10

15,45,100

Looking for 45 in A10:C10, we will get: matthew

These results are totally justified. Looks like you have a similar situation.

 

[matthewlouis]

OK, thanks, Aladin . . . looks I will need to do it a different way because there is data I need from both lines, i.e., the 45 in your example = the 6.35% in my example. However, there are 4 other cells on that row that I want to return the date to me for and I am using the 6.35% (or 45 in your example). Perhaps I needs to use another "looking for" part. Thanks for your help!

 

[Aladin Akyurek]

You might want to post a small portion of these rows along with the desired results.

 

[matthewlouis]

<colgroup><col style="mso-width-source:userset;mso-width-alt:25888;width:607pt" width="809"> <col style="mso-width-source:userset;mso-width-alt:7712;width:181pt" width="241"> <col style="mso-width-source:userset;mso-width-alt:6976;width:164pt" width="218"> <col style="mso-width-source:userset;mso-width-alt:7488;width:176pt" width="234"> <col style="mso-width-source:userset;mso-width-alt:7840;width:184pt" width="245"> <col style="mso-width-source:userset;mso-width-alt:43520; width:1020pt" width="1360"> <col style="mso-width-source:userset;mso-width-alt:6272;width:147pt" width="196"> <col style="mso-width-source:userset;mso-width-alt:9216;width:216pt" width="288"> </colgroup><tbody>
[TD="class: xl104, width: 809"]Daily SP Low[/TD]
[TD="class: xl102, width: 241"]2/9/2018[/TD]
[TD="class: xl100, width: 218"]2,532.69[/TD]
[TD="class: xl100, width: 234"]2,638.67[/TD]
[TD="class: xl100, width: 245"]2,639.40[/TD]
[TD="class: xl101, width: 1360"]Hasn't Closed Under SPL[/TD]
[TD="class: xl103, width: 196"] [/TD]
[TD="class: xl99, width: 288"]-4.04%[/TD]

[TD="class: xl104, width: 809"]Intermediate SP Low[/TD]
[TD="class: xl102, width: 241"]2/5/2018[/TD]
[TD="class: xl100, width: 218"]2,532.69[/TD]
[TD="class: xl100, width: 234"]2,763.39[/TD]
[TD="class: xl100, width: 245"]2,639.40[/TD]
[TD="class: xl101, width: 1360"]Hasn't Closed Under SPL[/TD]
[TD="class: xl103, width: 196"]


[/TD]
[TD="class: xl99, width: 288"]-4.04%[/TD]

</tbody>

Aladin, 2,532 is the low and the current price of 2,639 is -4.04% away from the 2,532. However, one is Daily SP Low and the other is an Intermediate SP Low. In another summary table, I am Index and Matching the -4.04% to try and retrieve both rows information. The problem is it's only returning the first row's information and NOT the second row.

Hope that helps! What do you think?

 

[Aladin Akyurek]

<colgroup><col style="mso-width-source:userset;mso-width-alt:25888;width:607pt" width="809"> <col style="mso-width-source:userset;mso-width-alt:7712;width:181pt" width="241"> <col style="mso-width-source:userset;mso-width-alt:6976;width:164pt" width="218"> <col style="mso-width-source:userset;mso-width-alt:7488;width:176pt" width="234"> <col style="mso-width-source:userset;mso-width-alt:7840;width:184pt" width="245"> <col style="mso-width-source:userset;mso-width-alt:43520; width:1020pt" width="1360"> <col style="mso-width-source:userset;mso-width-alt:6272;width:147pt" width="196"> <col style="mso-width-source:userset;mso-width-alt:9216;width:216pt" width="288"> </colgroup><tbody>
[TD="class: xl104, width: 809"]Daily SP Low[/TD]
[TD="class: xl102, width: 241"]2/9/2018[/TD]
[TD="class: xl100, width: 218"]2,532.69[/TD]
[TD="class: xl100, width: 234"]2,638.67[/TD]
[TD="class: xl100, width: 245"]2,639.40[/TD]
[TD="class: xl101, width: 1360"]Hasn't Closed Under SPL[/TD]
[TD="class: xl103, width: 196"] [/TD]
[TD="class: xl99, width: 288"]-4.04%[/TD]

[TD="class: xl104, width: 809"]Intermediate SP Low[/TD]
[TD="class: xl102, width: 241"]2/5/2018[/TD]
[TD="class: xl100, width: 218"]2,532.69[/TD]
[TD="class: xl100, width: 234"]2,763.39[/TD]
[TD="class: xl100, width: 245"]2,639.40[/TD]
[TD="class: xl101, width: 1360"]Hasn't Closed Under SPL[/TD]
[TD="class: xl103, width: 196"]


[/TD]
[TD="class: xl99, width: 288"]-4.04%[/TD]

</tbody>

Aladin, 2,532 is the low and the current price of 2,639 is -4.04% away from the 2,532. However, one is Daily SP Low and the other is an Intermediate SP Low. In another summary table, I am Index and Matching the -4.04% to try and retrieve both rows information. The problem is it's only returning the first row's information and NOT the second row.

Hope that helps! What do you think?

It's up to you to re-map what follows to your actual workbook.

Let A2:H3 of Sheet1 house the above data.

Let A1 of Sheet2 house -4.04%, the percentage of interest.

In A2 of Sheet2 enter:

=COUNTIFS(Sheet1!$H$2:$H$3,$A$1)

In A4 control+shift+enter, not just enter:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$H$2:$H$3=$A$1,ROW(Sheet1!$H$2:$H$3)-ROW(Sheet1!$H$2)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!A$2:A$3,$A4))

 

[matthewlouis]

Hmmmm, if A1:A16 of Sheet2 contains all the percentages (A1= -4.04%) and I copy the COUNTIF formula in A2 of Sheet2, it gives me a number (1) for how many times that number occurs. However, that A column in Sheet2 has all the percentages in it . . . my souce data. Then in A4 of Sheet2, you say to do the control+shift+enter formula. Am I missing something, because what you're saying to do is wiping out my source data in Sheet2 (where the -4.04% is housed and all other percentages following it).

Is there a column off or something? I have mapped it every which or way and it doesn't work.

Thanks for you help on this, Aladin.

 

[Aladin Akyurek]

@ matthewlouis

Here is the workbook which implements the set up

https://www.dropbox.com/s/jd4rbblrrhb25sa/matthewlouis%20sublist.xlsx?dl=0

that you can adapt to your worbook.