Hi All,
I'm having a problem trying to return some data. This is the source data table:
<table border="0" cellpadding="0" cellspacing="0" height="236" width="311"><colgroup><col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:71pt" height="20" width="95" align="center">Return Values</td> <td style="width:75pt" width="100" align="center">ID's</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1</td> <td align="center">9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">2</td> <td align="center">985</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">3</td> <td align="center">785</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">4</td> <td align="center">786</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">5</td> <td align="center">244</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">6</td> <td align="center">156</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">7</td> <td align="center">1040;1041;1042</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">8</td> <td align="center">706</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">1040;1539</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">10</td> <td align="center">376</td> </tr> </tbody></table>
Here is the output table:
<table border="0" cellpadding="0" cellspacing="0" height="112" width="189"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">value(s) 1
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">985</td> <td align="center">value(s) 2
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">value(s) 3
</td> </tr> </tbody></table>
I'm using the following formula to populate the "Values" column in the output table:
=LOOKUP(9.99999999999999E+307,SEARCH("Lookup ID address","range of Return Values"),"range of ID's")
This formula somewhat works and outputs the following:
<table border="0" cellpadding="0" cellspacing="0" height="96" width="213"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">985</td> <td align="center">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">9</td> </tr> </tbody></table>
Problem 1) When the Lookup ID is 9, it returns the Values 9 as is last saw a 9 in that ID's cell, however I need this to exactly match 9 only. In this case it should only return 1 as per the table below:
<table border="0" cellpadding="0" cellspacing="0" height="56" width="183"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">1</td> </tr> </tbody></table>
Problem 2) I need to return multiple Values if the Lookup ID is found multiple times. For example 1040 is found twice, so this should return 7,9 as below
<table border="0" cellpadding="0" cellspacing="0" height="56" width="200"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">7,9</td> </tr> </tbody></table>
A working output data table would look like this with the source data table provided in the beginning:
<table border="0" cellpadding="0" cellspacing="0" height="116" width="219"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">985</td> <td align="center">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">7,9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">786</td> <td align="center">4</td> </tr> </tbody></table>
Thanks for your help on this one!!!!!
I'm having a problem trying to return some data. This is the source data table:
<table border="0" cellpadding="0" cellspacing="0" height="236" width="311"><colgroup><col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:71pt" height="20" width="95" align="center">Return Values</td> <td style="width:75pt" width="100" align="center">ID's</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1</td> <td align="center">9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">2</td> <td align="center">985</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">3</td> <td align="center">785</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">4</td> <td align="center">786</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">5</td> <td align="center">244</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">6</td> <td align="center">156</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">7</td> <td align="center">1040;1041;1042</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">8</td> <td align="center">706</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">1040;1539</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">10</td> <td align="center">376</td> </tr> </tbody></table>
Here is the output table:
<table border="0" cellpadding="0" cellspacing="0" height="112" width="189"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">value(s) 1
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">985</td> <td align="center">value(s) 2
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">value(s) 3
</td> </tr> </tbody></table>
I'm using the following formula to populate the "Values" column in the output table:
=LOOKUP(9.99999999999999E+307,SEARCH("Lookup ID address","range of Return Values"),"range of ID's")
This formula somewhat works and outputs the following:
<table border="0" cellpadding="0" cellspacing="0" height="96" width="213"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">985</td> <td align="center">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">9</td> </tr> </tbody></table>
Problem 1) When the Lookup ID is 9, it returns the Values 9 as is last saw a 9 in that ID's cell, however I need this to exactly match 9 only. In this case it should only return 1 as per the table below:
<table border="0" cellpadding="0" cellspacing="0" height="56" width="183"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">1</td> </tr> </tbody></table>
Problem 2) I need to return multiple Values if the Lookup ID is found multiple times. For example 1040 is found twice, so this should return 7,9 as below
<table border="0" cellpadding="0" cellspacing="0" height="56" width="200"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">7,9</td> </tr> </tbody></table>
A working output data table would look like this with the source data table provided in the beginning:
<table border="0" cellpadding="0" cellspacing="0" height="116" width="219"><colgroup><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:57pt" height="20" width="76" align="center">Lookup ID</td> <td style="width:48pt" width="64" align="center">Values</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">9</td> <td align="center">1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">985</td> <td align="center">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">1040</td> <td align="center">7,9</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="center">786</td> <td align="center">4</td> </tr> </tbody></table>
Thanks for your help on this one!!!!!
