Lookup From Partial Number (From Left)

[muhammad susanto]

hi all...

how to lookup data with partial match count of number like this :
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th></th><th> </th><th> </th><th>lookup</th><th>result</th></tr></thead><tbody>
<tr><td>data1</td><td>data2</td><td> </td><td>4010101001</td><td>BUILDING A</td></tr>
<tr><td>BUILDING A</td><td>4010101</td><td> </td><td> </td><td> </td></tr>
<tr><td>BUILDING B</td><td>4010102</td><td> </td><td> </td><td> </td></tr>
<tr><td>BUILDING C</td><td>4010103</td><td> </td><td> </td><td> </td></tr>
<tr><td>BUILDING D</td><td>4010104</td><td> </td><td> </td><td></td></tr>
</tbody></table>

my len data lookup is 7 (from left first), total len = 10
any help, thank is advance..

susanto

 

[jtakw]

Hi,

Assuming Column B contains Real Numbers, if not, let me know:

Book1
	A	B	C	D	E
1				lookup	result
2	data1	data2		4010101001	BUILDING A
3	BUILDING A	4010101
4	BUILDING B	4010102
5	BUILDING C	4010103
6	BUILDING D	4010104
Sheet173

Cell Formulas
Range	Formula
E2	=INDEX(A3:A6,MATCH(LEFT(D2,7)+0,B3:B6,0))