"double" INDEX & MATCH search?

jxb

Board Regular
Joined
Apr 19, 2007
Messages
132
Office Version
2010
Platform
Windows
to all

I am trying to do a "double" search with Index & MATCH
I want to return the value in the header (row 7) corresponding to the max value - See attached test
I find the max value - cell c2
and find the ID corresponding to the max value (cell c3) using a match
I know would like to return the case (row 7) corresponding to the max value & ID - in the test v5

This is a test but I'd any (row) range dynamic. Is this possible with INDIRECT,INDEX & MATCH?

How can this be done?

Thanks in advance
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jxb

Board Regular
Joined
Apr 19, 2007
Messages
132
Office Version
2010
Platform
Windows
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">max =</td><td style="text-align: right;;">0.933328</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">ID =</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">case =</td><td style="text-align: right;;"></td><td style=";">return the  value in row 7 corrpsonding to the max value for the ID found. In this case v5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">ID</td><td style="text-align: center;;">max</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">v1</td><td style="text-align: center;background-color: #FFFF00;;">v2</td><td style="text-align: center;background-color: #FFFF00;;">v3</td><td style="text-align: center;background-color: #FFFF00;;">v4</td><td style="text-align: center;background-color: #FFFF00;;">v5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.909336</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.594634</td><td style="text-align: right;;">0.356569</td><td style="text-align: right;;">0.909336</td><td style="text-align: right;;">0.813421</td><td style="text-align: right;;">0.649909</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.885865</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.06891</td><td style="text-align: right;;">0.885865</td><td style="text-align: right;;">0.472114</td><td style="text-align: right;;">0.323692</td><td style="text-align: right;;">0.595647</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.491332</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.086735</td><td style="text-align: right;;">0.164288</td><td style="text-align: right;;">0.46222</td><td style="text-align: right;;">0.013518</td><td style="text-align: right;;">0.491332</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">78</td><td style="text-align: right;;">0.933328</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.557731</td><td style="text-align: right;;">0.659797</td><td style="text-align: right;;">0.817564</td><td style="text-align: right;;">0.797375</td><td style="text-align: right;;">0.933328</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">99</td><td style="text-align: right;;">0.918975</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.192214</td><td style="text-align: right;;">0.512669</td><td style="text-align: right;;">0.910371</td><td style="text-align: right;;">0.201446</td><td style="text-align: right;;">0.918975</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=MAX(<font color="Blue">C8:C12</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=INDEX(<font color="Blue">B8:B12,MATCH(<font color="Red">C2,C8:C12,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C8</th><td style="text-align:left">=MAX(<font color="Blue">E8:I8</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">=MAX(<font color="Blue">E9:I9</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C10</th><td style="text-align:left">=MAX(<font color="Blue">E10:I10</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C11</th><td style="text-align:left">=MAX(<font color="Blue">E11:I11</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">=MAX(<font color="Blue">E12:I12</font>)</td></tr></tbody></table></td></tr></table><br />
 

jxb

Board Regular
Joined
Apr 19, 2007
Messages
132
Office Version
2010
Platform
Windows
Had a go with one of the tools. See posted HTML above - A pain that one cannot attach xls directly anymore!
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,746
Office Version
365
Platform
Windows
A pain that one cannot attach xls directly anymore!
It never was possible.
 

jxb

Board Regular
Joined
Apr 19, 2007
Messages
132
Office Version
2010
Platform
Windows
Well it's been while and I am clearly going old then... Only managed to figure out about the copy-paste html thing!
Let's see if someone can get a smart way of extracting the data
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
How about
=INDEX(E7:I7,SUMPRODUCT((E8:I12=MAX(E8:I12))*COLUMN(E8:I12))-COLUMN(E8:I12)+1)
 
  • Like
Reactions: jxb

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,922
Two more options:

=INDEX(E7:I7,MATCH(C2,INDEX(E8:I12,MATCH(C2,C8:C12,0),0),0))

which assumes the C8:C12 values are present. Or

=INDEX(E7:I7,AGGREGATE(15,6,(COLUMN(E8:I12)-COLUMN(E8)+1)/(E8:I12=C2),1))

which just reads the E8:I12 values directly.

Note that in the unlikely event of a tie of the maximum value, Fluff's formula will return an error or an incorrect value, while these formulas will return the first match.
 
Last edited:
  • Like
Reactions: jxb

jxb

Board Regular
Joined
Apr 19, 2007
Messages
132
Office Version
2010
Platform
Windows
Thanks. Will take time to breakdown the formula to understand it. In the meantime I'll definitely save it for future use

Q: Is there not a way of only working on the row where the ID has been found? In other word I want to make use of the fact that I know the ID to work with
Why: For the test the selecting the whole array is fine I ultimately will have a number of max/ID/case to be found each wording on a subset of the data set. See example below
Mind you the test i just did based on your formula seems to work, so maybe I am over-thinking the whole think (as your solution seem to do the trick)

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">max1=</td><td style="text-align: right;;">0.933328</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">ID1 =</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">case =</td><td style=";">v5</td><td style=";">return the  value in row 7 corrpsonding to the max value for the ID found. In this case v5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">max2=</td><td style="text-align: right;;">0.994172</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">ID1 =</td><td style="text-align: right;;">103</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">case =</td><td style=";">v4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;;">ID</td><td style="text-align: center;;">max</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #FFFF00;;">v1</td><td style="text-align: center;background-color: #FFFF00;;">v2</td><td style="text-align: center;background-color: #FFFF00;;">v3</td><td style="text-align: center;background-color: #FFFF00;;">v4</td><td style="text-align: center;background-color: #FFFF00;;">v5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.909336</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.595</td><td style="text-align: right;;">0.357</td><td style="text-align: right;;">0.909</td><td style="text-align: right;;">0.813</td><td style="text-align: right;;">0.650</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.885865</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.565</td><td style="text-align: right;;">0.886</td><td style="text-align: right;;">0.472</td><td style="text-align: right;;">0.324</td><td style="text-align: right;;">0.596</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.8797</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.087</td><td style="text-align: right;;">0.164</td><td style="text-align: right;;">0.880</td><td style="text-align: right;;">0.014</td><td style="text-align: right;;">0.491</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">78</td><td style="text-align: right;;">0.933328</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.558</td><td style="text-align: right;;">0.660</td><td style="text-align: right;;">0.818</td><td style="text-align: right;;">0.797</td><td style="text-align: right;;">0.933</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;border-bottom: 1px solid black;;">99</td><td style="text-align: right;border-bottom: 1px solid black;;">0.918975</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;">0.192</td><td style="text-align: right;border-bottom: 1px solid black;;">0.513</td><td style="text-align: right;border-bottom: 1px solid black;;">0.910</td><td style="text-align: right;border-bottom: 1px solid black;;">0.201</td><td style="text-align: right;border-bottom: 1px solid black;;">0.919</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;;">101</td><td style="text-align: right;border-top: 1px solid black;;">0.98562</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;">0.986</td><td style="text-align: right;border-top: 1px solid black;;">0.637</td><td style="text-align: right;border-top: 1px solid black;;">0.394</td><td style="text-align: right;border-top: 1px solid black;;">0.041</td><td style="text-align: right;border-top: 1px solid black;;">0.774</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">102</td><td style="text-align: right;;">0.837432</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.557</td><td style="text-align: right;;">0.216</td><td style="text-align: right;;">0.154</td><td style="text-align: right;;">0.688</td><td style="text-align: right;;">0.837</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">103</td><td style="text-align: right;;">0.994172</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.235</td><td style="text-align: right;;">0.796</td><td style="text-align: right;;">0.550</td><td style="text-align: right;;">0.994</td><td style="text-align: right;;">0.234</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">104</td><td style="text-align: right;;">0.950287</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.613</td><td style="text-align: right;;">0.480</td><td style="text-align: right;;">0.556</td><td style="text-align: right;;">0.950</td><td style="text-align: right;;">0.056</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=MAX(<font color="Blue">C11:C15</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=INDEX(<font color="Blue">B11:B15,MATCH(<font color="Red">C2,C11:C15,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">=INDEX(<font color="Blue">E10:I10,SUMPRODUCT(<font color="Red">(<font color="Green">E11:I15=MAX(<font color="Purple">E11:I15</font>)</font>)*COLUMN(<font color="Green">E11:I15</font>)</font>)-COLUMN(<font color="Red">E11:I15</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">=MAX(<font color="Blue">C16:C19</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=INDEX(<font color="Blue">B16:B19,MATCH(<font color="Red">C5,C16:C19,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C7</th><td style="text-align:left">=INDEX(<font color="Blue">E10:I10,SUMPRODUCT(<font color="Red">(<font color="Green">E11:I19=MAX(<font color="Purple">E11:I19</font>)</font>)*COLUMN(<font color="Green">E11:I19</font>)</font>)-COLUMN(<font color="Red">E11:I19</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C11</th><td style="text-align:left">=MAX(<font color="Blue">E11:I11</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">=MAX(<font color="Blue">E12:I12</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C13</th><td style="text-align:left">=MAX(<font color="Blue">E13:I13</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C14</th><td style="text-align:left">=MAX(<font color="Blue">E14:I14</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C15</th><td style="text-align:left">=MAX(<font color="Blue">E15:I15</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C16</th><td style="text-align:left">=MAX(<font color="Blue">E16:I16</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C17</th><td style="text-align:left">=MAX(<font color="Blue">E17:I17</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C18</th><td style="text-align:left">=MAX(<font color="Blue">E18:I18</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C19</th><td style="text-align:left">=MAX(<font color="Blue">E19:I19</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
Eric's 1st formula will just look at the relevant row.
Also as he pointed out, if it's possible to have the same value twice, my suggestion will fail
 

Watch MrExcel Video

Forum statistics

Threads
1,102,867
Messages
5,489,365
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top