UDF to search in three columns

[khawarameer]

Dear All

I need to create a UDF to extract an output from desired cell in a five column table. Please refer below two sheets.

table.tableizer-table {
    border: 1px solid #CCC; font-family: ;
    font-size: 12px;
} 
.tableizer-table td {
    padding: 4px;
    margin: 3px;
    border: 1px solid #ccc;
}
.tableizer-table th {
    background-color: #104E8B; 
    color: #FFF;
    font-weight: bold;

Table 1
   
  Table 2 Title #DateDebitCreditBalance Title NoValueDate5880200044600107-03-2013 10,000.0010,000.00 5880200913580125-03-20135880200044600126-04-20135,000.00 5,000.00 5880200913580129-03-20135880200044600129-04-20133,000.00 2,000.00 5880200913580109-04-20135880200913580125-02-2013 5,000.005,000.00 5880200913580111-04-20135880200913580125-03-2013 28,000.0033,000.00 5880200913580112-04-20135880200913580125-03-201330,000.00 3,000.00 5880200913580116-04-20135880200913580126-03-2013 60,000.0063,000.00 5880200913580122-04-20135880200913580127-03-201360,000.00 3,000.00 5880200913580122-04-20135880200913580127-03-2013120.00 2,880.00 5880201519110111-03-20135880200913580128-03-2013 17,000.0019,880.00 5880201519110126-03-20135880200913580128-03-2013 38,000.0057,880.00 5880202133770106-02-20135880200913580129-03-201365,000.00 -7,120.00 5880202133980102-01-20135880200913580129-03-2013 10,000.002,880.00 5880202133980106-02-20135880200913580101-04-2013 40,000.0042,880.00 5880202135020126-02-20135880200913580101-04-201330,000.00 12,880.00 5880202135020116-03-20135880200913580104-04-201310,000.00 2,880.00 5880202135020110-04-20135880200913580108-04-2013 39,690.0042,570.00 5880202138250126-12-20125880200913580108-04-201330,000.00 12,570.00 5880202138250103-01-20135880200913580108-04-2013 50,000.0062,570.00 5880202138250107-01-20135880200913580109-04-201360,000.00 2,570.00 5880202138250104-02-20135880200913580110-04-2013 20,000.0022,570.00 5880202138250120-02-20135880200913580111-04-2013 55,000.0077,570.00 5880202138250120-02-20135880200913580111-04-201365,000.00 12,570.00 5880202138250119-03-20135880200913580112-04-2013 97,000.00109,570.00 5880202138250103-04-20135880200913580112-04-201363,860.00 45,710.00 5880202138250110-04-20135880200913580112-04-2013 25,000.0070,710.00 5880202138310120-02-20135880200913580112-04-201336,000.00 34,710.00 5880202138460124-12-201258802009135801
13-04-201310,000.00 24,710.00 5880202138460126-12-2012

I want to find value of column 5 of table 1 on the basis of value in table 2.
I need a UDF which will search each Title No from table2 in Title No of table 1, once a match is found then search the valuedate of table1 in the date column of table2 starting from row number of previous search and downwards, once a match is found go to respective column five of table 1 and obtain the immediate upper cell value of column five.

I hope you guys will help soon.

Regards

 

[khawarameer]

I think tables were not visible in the post.
here they are.

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: <?php echo $tableFont ?>;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>

Table 1						Table 2
Title #	Date	Debit	Credit	Balance		Title No	ValueDate
58802000446001	07-03-2013		10,000.00	10,000.00		58802009135801	25-03-2013
58802000446001	26-04-2013	5,000.00		5,000.00		58802009135801	29-03-2013
58802000446001	29-04-2013	3,000.00		2,000.00		58802009135801	09-04-2013
58802009135801	25-02-2013		5,000.00	5,000.00		58802009135801	11-04-2013
58802009135801	25-03-2013		28,000.00	33,000.00		58802009135801	12-04-2013
58802009135801	25-03-2013	30,000.00		3,000.00		58802009135801	16-04-2013
58802009135801	26-03-2013		60,000.00	63,000.00		58802009135801	22-04-2013
58802009135801	27-03-2013	60,000.00		3,000.00		58802009135801	22-04-2013
58802009135801	27-03-2013	120.00		2,880.00		58802015191101	11-03-2013
58802009135801	28-03-2013		17,000.00	19,880.00		58802015191101	26-03-2013
58802009135801	28-03-2013		38,000.00	57,880.00		58802021337701	06-02-2013
58802009135801	29-03-2013	65,000.00		-7,120.00		58802021339801	02-01-2013
58802009135801	29-03-2013		10,000.00	2,880.00		58802021339801	06-02-2013
58802009135801	01-04-2013		40,000.00	42,880.00		58802021350201	26-02-2013
58802009135801	01-04-2013	30,000.00		12,880.00		58802021350201	16-03-2013
58802009135801	04-04-2013	10,000.00		2,880.00		58802021350201	10-04-2013
58802009135801	08-04-2013		39,690.00	42,570.00		58802021382501	26-12-2012
58802009135801	08-04-2013	30,000.00		12,570.00		58802021382501	03-01-2013
58802009135801	08-04-2013		50,000.00	62,570.00		58802021382501	07-01-2013
58802009135801	09-04-2013	60,000.00		2,570.00		58802021382501	04-02-2013
58802009135801	10-04-2013		20,000.00	22,570.00		58802021382501	20-02-2013
58802009135801	11-04-2013		55,000.00	77,570.00		58802021382501	20-02-2013
58802009135801	11-04-2013	65,000.00		12,570.00		58802021382501	19-03-2013
58802009135801	12-04-2013		97,000.00	109,570.00		58802021382501	03-04-2013
58802009135801	12-04-2013	63,860.00		45,710.00		58802021382501	10-04-2013
58802009135801	12-04-2013		25,000.00	70,710.00		58802021383101	20-02-2013
58802009135801	12-04-2013	36,000.00		34,710.00		58802021384601	24-12-2012
58802009135801	13-04-2013	10,000.00		24,710.00		58802021384601	26-12-2012

<tbody>
</tbody>

 

[Marcelo Branco]

Hi

Could you show us the expected results for some rows?

M.

 

[khawarameer]

Take example to row 2 of table 2, when title no will be searched in column 1 of table 1 it will first match at row 4 of table 1, then date mentioned in column 2 row2 of table 1 will be searched in column 2 of table 1 which will first match at row 12 of table 1, then goto column five of table 1 and display value of cell just above the current cell that will be 57,880.00.

I think this will be helpful.

 

[Marcelo Branco]

Take example to row 2 of table 2, when title no will be searched in column 1 of table 1 it will first match at row 4 of table 1, then date mentioned in column 2 row2 of table 1 will be searched in column 2 of table 1 which will first match at row 12 of table 1, then goto column five of table 1 and display value of cell just above the current cell that will be 57,880.00.

I think this will be helpful.

Sorry, i'm not following you.
The first match of Title is in row 5 (considering headers in row 1)
As far i can see the date in column 2 of Table 2 (25-03-2013) can be found first in row 6
In row 12 the date is 28-03-2013, so why are you using this row?

M.

 

[khawarameer]

I just gave example actually i want to search all rows of table 2 in table 1.

 

[Marcelo Branco]

I just gave example actually i want to search all rows of table 2 in table 1.

Ok, i understood that but it's stil not clear for me what you need.

So, for example, searching row 2 of Table 2, ie,

58802009135801

25/03/2013

<colgroup><col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;" width="106"> <col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" width="75"> <tbody>
</tbody>

The Title# matches row 5 (first match) and the date matches row 6.

Shouldn´t the expected result be 33,000.00 (column 5 of Table 1)?

M.

 

[khawarameer]

actually yes, this should be the result. I must have got confused. Anyway can you now give the solution of it?

 

[Marcelo Branco]

A possible solution using formulas

Assuming Table 1 in columns A - E; Table 2 in columns G:H; headers in row 1, try this array formula in I2 (Excel 2007 or higher)

=IFERROR(INDEX($E$2:$E$500,MATCH(1,IF($A$2:$A$500=G2,IF($B$2:$B$500=H2,1)),0)),"Not Found")

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter

copy down

Hope this is what you need.

M.

 

[khawarameer]

A possible solution using formulas

Assuming Table 1 in columns A - E; Table 2 in columns G:H; headers in row 1, try this array formula in I2 (Excel 2007 or higher)

=IFERROR(INDEX($E$2:$E$500,MATCH(1,IF($A$2:$A$500=G2,IF($B$2:$B$500=H2,1)),0)),"Not Found")

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter

copy down

Hope this is what you need.

M.

Dear Marcelo,

"Not Found" message is displayed at every result.