Finding 1st, 2nd, 3rd... Nth value by excel formula (vlookup/if/macth..)

jgopalk

New Member
Joined
Jun 14, 2011
Messages
26
Hi there,

I just wanted to fix the following problem that last few days struck my work.

Data:
ABCDEFGHIJK
110/5/2016RAM10/5/2016RAMRAHIMSUJA0000
210/5/2016RAHIM10/6/2016RAMRAHIMSUJASANGEETHA000
310/5/2016SUJA10/10/2016RAMRAHIMSUJASANGEETHAJOHN00
410/6/2016RAM
510/6/2016RAHIM
610/6/2016SUJA
710/6/2016SANGEETHA
810/10/2016RAM
910/10/2016RAHIM
1010/10/2016SUJA
1110/10/2016SANGEETHA
1210/10/2016JOHN

<tbody>
</tbody>

Black font is available data and red colour font is required data by formula.

This is for huge data so we can not change formula each time and each look up cells. kindly try to fix it by formulas and not by<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> code..

Kindly check and fix it.

Thanks,
Gopal
 

jgopalk

New Member
Joined
Jun 14, 2011
Messages
26
The names i have given in order. But it is not the exact case. Names might be interchanged.
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
365
Platform
Windows
If you don't mind the use of a helper column (I would recommend using it), then perhaps the below would work for you.

Excel Workbook
ABCDEFGHIJKL
1HelperDateName*DatesNamesNamesNamesNamesNamesNamesNames
210/5/2016RAM*10/5/2016RAHIMSUJA0000
342648-210/5/2016RAHIM*10/6/2016RAMRAHIMSUJASANGEETHA000
442648-310/5/2016SUJA*10/10/2016RAMRAHIMSUJASANGEETHAJOHN00
542649-110/6/2016RAM*********
642649-210/6/2016RAHIM*********
742649-310/6/2016SUJA*********
842649-410/6/2016SANGEETHA*********
942653-110/10/2016RAM*********
1042653-210/10/2016RAHIM*********
1142653-310/10/2016SUJA*********
1242653-410/10/2016SANGEETHA*********
1342653-510/10/2016JOHN*********
Sheet3
 
Last edited:

jgopalk

New Member
Joined
Jun 14, 2011
Messages
26
Thank you very much Mr. Virus :)

It is awesome. This is what I was expecting. Great work.

Thanks,
Gopal
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,304
Office Version
365
Platform
Windows
Without a helper column ..

D2 is copied down.
E2 is copied across and down.

<b>Excel 2010 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</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: #161120;text-align: center;">1</td><td style="text-align: right;;">Date</td><td style=";">Name</td><td style="text-align: right;;"></td><td style="text-align: right;;">Dates</td><td style=";">Names</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: #161120;text-align: center;">2</td><td style="text-align: right;;">5/10/2016</td><td style=";">RAM</td><td style="text-align: right;;"></td><td style="text-align: right;;">5/10/2016</td><td style=";">RAM</td><td style=";">RAHIM</td><td style=";">SUJA</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5/10/2016</td><td style=";">RAHIM</td><td style="text-align: right;;"></td><td style="text-align: right;;">6/10/2016</td><td style=";">RAM</td><td style=";">RAHIM</td><td style=";">SUJA</td><td style=";">SANGEETHA</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">5/10/2016</td><td style=";">SUJA</td><td style="text-align: right;;"></td><td style="text-align: right;;">10/10/2016</td><td style=";">RAM</td><td style=";">RAHIM</td><td style=";">SUJA</td><td style=";">SANGEETHA</td><td style=";">JOHN</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">6/10/2016</td><td style=";">RAM</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">6/10/2016</td><td style=";">RAHIM</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6/10/2016</td><td style=";">SUJA</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">6/10/2016</td><td style=";">SANGEETHA</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">10/10/2016</td><td style=";">RAM</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">10/10/2016</td><td style=";">RAHIM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10/10/2016</td><td style=";">SUJA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">10/10/2016</td><td style=";">SANGEETHA</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: #161120;text-align: center;">13</td><td style="text-align: right;;">10/10/2016</td><td style=";">JOHN</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: #161120;text-align: center;">14</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><td style="text-align: right;;"></td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">List by date</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=IF(<font color="#0000FF">D1="","",IF(<font color="#FF0000">D1=MAX(<font color="#00FF00">A$2:A$13</font>),"",SMALL(<font color="#00FF00">A$2:A$13,COUNTIF(<font color="#800080">A$2:A$13,"<="&D1</font>)+1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=IF(<font color="#0000FF">$D2="","",IF(<font color="#FF0000">COLUMNS(<font color="#00FF00">$E2:E2</font>)>COUNTIF(<font color="#00FF00">$A$2:$A$13,$D2</font>),0,INDEX(<font color="#00FF00">$B$2:$B$13,MATCH(<font color="#800080">$D2,$A$2:$A$13,0</font>)+COLUMNS(<font color="#800080">$E2:E2</font>)-1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top