Index and Match Duplicates

matthewlouis

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
374
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 :)
 
Last edited:
Sort by date Sort by votes
I think I fail to get thru.

Do not talk about the formula you already have.

Just say this is my input and post the input.

Then say this the output that I want from that input and post that output.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
No, it's probably me . . . I don't understand sometimes :)

OK, this is my input or what I am calling the source sheet.

Time Frame, Swing Point, Wide Price (WP), High Volume (HV)DateLowHighCurrent PriceStatusBars Taken for SP TestTests -- Support and Resistance
Daily SP High3/21/20182,709.792,739.142,670.93Hasn't Closed Over SPH2.55%
Daily SP High3/13/20182,758.682,801.902,670.93Hasn't Closed Over SPH4.90%
Daily SP High2/27/20182,744.222,789.152,670.93Hasn't Closed Over SPH4.43%
Daily SP Low3/2/20182,647.322,696.252,670.93Failed1Failed
Daily SP Low4/10/20182,635.782,665.452,670.93Failed1Failed
Daily SP Low4/2/20182,553.802,638.302,670.93Hasn't Closed Under SPL-4.39%
Daily SP Low2/9/20182,532.692,638.672,670.93Hasn't Closed Under SPL-5.18%
Daily SP High4/18/20182,703.632,717.492,670.93Hasn't Closed Over SPH1.74%
Daily Wide Price and High Volume2/9/20182,532.692,638.672,670.93Daily Wide Price and High Volume-1.21%
Daily Wide Price and High Volume2/6/20182,593.072,701.042,670.93Daily Wide Price and High Volume-3.00%
Daily Wide Price and High Volume2/8/20182,580.562,685.272,670.93Daily Wide Price and High Volume-3.50%
Intermediate SP High1/22/20182,808.122,872.872,670.93Hasn't Closed Over SPH7.56%
Intermediate SP High3/12/20182,741.472,801.902,670.93Hasn't Closed Over SPH4.90%
Intermediate SP Low11/13/20172,557.452,590.092,670.93Hasn't Closed Under SPL-4.25%
Intermediate SP Low2/5/20182,532.692,763.392,670.93Hasn't Closed Under SPL-5.18%
Intermediate SP Low10/2/20172,520.402,552.512,670.93Hasn't Closed Under SPL-5.64%
Intermediate SP Low8/21/20172,417.352,454.772,670.93Hasn't Closed Under SPL-9.49%
Intermediate SP Low6/26/20172,405.702,450.422,670.93Hasn't Closed Under SPL-9.93%
Intermediate SP Low5/15/20172,352.722,405.772,670.93Hasn't Closed Under SPL-11.91%
Intermediate SP Low3/27/20172,322.252,370.422,670.93Hasn't Closed Under SPL-13.05%
Intermediate Wide Price and High Volume1/29/20182,759.972,870.622,670.93Intermediate Wide Price and High Volume3.23%
Intermediate Wide Price and High Volume2/26/20182,647.322,789.152,670.93Intermediate Wide Price and High Volume-0.89%
Intermediate Wide Price and High Volume11/27/20172,598.872,657.742,670.93Intermediate Wide Price and High Volume-0.49%
Intermediate Wide Price and High Volume2/5/20182,532.692,763.392,670.93Intermediate Wide Price and High Volume-5.46%
Monthly SP High5/1/20152,067.932,134.722,670.93Closed Over SPH ** Tested SPH High on HIGHER Volume ** Hasn't Failed2-20.08%
Monthly SP High11/1/20152,019.392,116.482,670.93Closed Over SPH ** Tested SPH High on LESS Volume ** Hasn't Failed3-24.39%
Monthly SP Low5/1/20172,352.722,418.712,670.93Hasn't Closed Under SPL-11.91%
Monthly SP Low11/1/20162,083.792,214.102,670.93Hasn't Closed Under SPL-21.98%
Monthly SP Low10/1/20141,820.662,018.192,670.93Hasn't Closed Under SPL-31.83%
Monthly SP Low2/1/20161,810.101,962.962,670.93Hasn't Closed Under SPL-32.23%
Monthly SP Low2/1/20141,737.921,867.922,670.93Hasn't Closed Under SPL-34.93%
Monthly SP Low8/1/20131,627.471,709.672,670.93Hasn't Closed Under SPL-39.07%
Monthly Wide Price and High Volume3/1/20161,937.092,072.212,670.93Monthly Wide Price and High Volume-22.42%
Monthly Wide Price and High Volume1/1/20161,812.292,038.202,670.93Monthly Wide Price and High Volume-23.69%
Monthly Wide Price and High Volume10/1/20141,820.662,018.192,670.93Monthly Wide Price and High Volume-24.44%
Monthly Wide Price and High Volume2/1/20161,810.101,962.962,670.93Monthly Wide Price and High Volume-26.51%

<tbody>
</tbody>
 
Last edited:
Upvote 0
This is my output

Intermediate SP High1/22/20182,808.122,872.877.55%1 Hasn't Closed Over SPH
17Daily SP High3/13/20182,758.682,801.904.89%2 Hasn't Closed Over SPH
34Intermediate SP High3/12/20182,741.472,801.904.89%2 Hasn't Closed Over SPH
Daily SP High2/27/20182,744.222,789.154.41%1 Hasn't Closed Over SPH
Intermediate Wide Price and High Volume1/29/20182,759.972,870.623.21%10Intermediate Wide Price and High Volume
Daily SP High3/21/20182,709.792,739.142.54%1 Hasn't Closed Over SPH
Daily SP High4/18/20182,703.632,717.491.73%1 Hasn't Closed Over SPH
Intermediate Wide Price and High Volume11/27/20172,598.872,657.74-0.51%10Intermediate Wide Price and High Volume
Intermediate Wide Price and High Volume2/26/20182,647.322,789.15-0.90%10Intermediate Wide Price and High Volume
Daily Wide Price and High Volume2/9/20182,532.692,638.67-1.22%10Daily Wide Price and High Volume
Daily Wide Price and High Volume2/6/20182,593.072,701.04-3.02%10Daily Wide Price and High Volume
Daily Wide Price and High Volume2/8/20182,580.562,685.27-3.51%10Daily Wide Price and High Volume
Intermediate SP Low11/13/20172,557.452,590.09-4.26%1 Hasn't Closed Under SPL
Daily SP Low4/2/20182,553.802,638.30-4.40%1 Hasn't Closed Under SPL
Daily SP Low2/9/20182,532.692,638.67-5.19%2 Hasn't Closed Under SPL
Daily SP Low2/9/20182,532.692,638.67-5.19%2 Hasn't Closed Under SPL

<colgroup><col style="mso-width-source:userset;mso-width-alt:2592;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:13344;width:313pt" width="417"> <col style="mso-width-source:userset;mso-width-alt:7520;width:176pt" width="235"> <col style="mso-width-source:userset;mso-width-alt:4352;width:102pt" width="136"> <col style="mso-width-source:userset;mso-width-alt:4320;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:5952;width:140pt" width="186"> <col style="mso-width-source:userset;mso-width-alt:3008;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:6528;width:153pt" width="204"> <col style="mso-width-source:userset;mso-width-alt:22752;width:533pt" width="711"> </colgroup><tbody>
</tbody>
 
Upvote 0
It's how far (as a precentage) that the current price is from the low or the high DEPENDING on what the last column says . . . example: if the last column says, "Hasn't Closed Over SP High" then it's the percentage the current price (found in the input table) is away from the High. Or, if it says "Hasn't Closed Under SP Low" the percentage is how far the current price is away from the low.
 
Last edited:
Upvote 0
"What is the rule by which we get the output?"

This means: What are we testing? Are we testing the Status column or are we testing the Tests -- Support and Resistance column?
 
Upvote 0
We are testing the current price against Support and Resistance columns -- these are the prices of significance, and these Support and Resistance prices are defined as to what they are in Column B and the date they occurred in Column C. The status column is the result that measures where the test is.

In the massive table with all the empty rows I posted, do you see 2,760 all the way down -- that is current price and that is what we're testing against Support and Resistance -- the lows and highs in that HUGE table with all the blank rows. This is where the percentage comes in -- how far away is the current price test from Support and Resistance?

Is the current price above the prices of significance's high? Or, is the current price below the price of significance's low?

Thanks for your patience -- I hope this is more precise in what you are asking, the way you asked it certainly helps.
 
Last edited:
Upvote 0
Hi Aladin, did you see my last post? Does that answer your question better? What do you think?
 
Last edited:
Upvote 0
matthewlouis said:
Hi Aladin, did you see my last post? Does that answer your question better? What do you think?
Click to expand...

1. Create a scaled-down sample (input) which does not contain irrelevant details (columns).
2. Create the output that must be obtained from the input.
3. State clearly what part of the output is computed (must be computed) and how.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
194
purekarting
P
C
  • Question
Index/Match | Index/Small Formula
Replies
5
Views
356
Eric W
Eric W
I
  • Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
136
StephenCrump
S
A
  • Solved
#REF! error when using INDEX MATCH
Replies
5
Views
133
Fluff
Fluff
J
  • Question
Index Match and Arrays
Replies
1
Views
432
Herakles
H

Forum statistics

Threads
1,215,375
Messages
6,124,578
Members
449,174
Latest member
chandan4057

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