Lookup the closest match

hirokona88 · Oct 4, 2009

Hello There,
I have come up with a real big problem. Initially i took help from Bob Umlas author of the books "it isnt excel its magic", and he has provided me with a really good solution. But the solution persists for fewer data only. whenever i am trying to apply it in a large table then a weird bug appears.
Ok here is the scenario.

I have got two tables, each containing 3 columns. That is, Table A has the columns, Time, Tel Number & Duration in sec, similarly table B also has these three columns Time, Tel Number and Duration in sec. Two tables are not equal. ****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Csamiul.huq%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5Csamiul.huq%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Csamiul.huq%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>[FONT=&quot]Now i need to lookup the respective numbers of sec from table b/a for table a/b. for example, in table A, at time 07:50 a tel number 01196025335 has 64 secs and 63 secs in the table b at time 07:51. for the same b# occured again in 08:53 having a time of 3 sec and the respective time in table 5 would be 2 sec occuring at 08:55 in table B. but when i do a simple lookup only one number shows up.[/FONT] again at 10:30 the same number 01196025335 appeared in table A but does not appeared in table B so the corrosponding value should stay as a blank.
that is i want to keep a clause which will lookup if the timevalue lies withing 5 minutes and then it will lookup the corrosponded value else it will ignore it.

Here is bob's solution for my problem:
=IF(INDEX(F:F,SMALL(IF(B4=$G$4:$G$86,ROW($4:$86)*(B4=$G$4:$G$86),""),COUNTIF($B$4:B4,B4)))-A4>TIMEVALUE("0:05:00"),"",INDEX($H$1:$H$86,SMALL(IF(B4=$G$4:$G$86,ROW($4:$86)*(B4=$G$4:$G$86),""),COUNTIF($B$4:B4,B4))))

where Column A=Time for table A
Column B= Tel Number for table A
Column C=Duration of table A
Column D contains the formula
Column F= Time for table B
Column G=Tel Number for table B
Column H=Duration of table B

As i have said, the formula worked fine for small tables, but cant work for large tables. so if you can write a macro or a custom function then it would be really helpful for me. Thank you. Waiting eagerly for a solution.

Regards,
Sami

Lookup the closest match

hirokona88

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

Lookup the closest match

hirokona88

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock