Lookup

[chadlaw32]

I am trying to look up a column based off a town and a service for example I want to lookup Victoria, C Basic, Start so the answer would be 19, or Munjor, D EAGLE DIGITAL, Reconnect so the result, any Ideas

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Service	Start	Install	Reconnect	Upgrade	Change (+)	Change (-)	Downgrade	Disconnect	Other	Net	End	Month End	Difference	Net			Add	Drop
2	011 - Victoria	Service											23	23	(23)				0	0
3	1	C EXPANDED BASIC	18	0	0	0	0	0	0	0	0	0	18	18	0	0			0	0
4	2	D ALL DIGITAL PROGRAMMING	11	0	0	0	0	0	0	0	0	0	11	11	0	0			0	0
5	3	C BASIC	19	0	0	0	0	0	0	0	0	0	19	19	0	0			0	0
6	6	F COPYRIGHT FEE	19	0	0	0	0	0	0	0	0	0	19	19	0	0			0	0
7	7	F FCC USER FEE	19	0	0	0	0	0	0	0	0	0	19	19	0	0			0	0
8	8	F RERTRANSMISSION FEE	19	0	0	0	0	0	0	0	0	0	19	19	0	0			0	0
9	9	H DIGITAL RECEIVER													0	0			0	0
10	10	H ADD'L DIG RECEIVER													0	0			0	0
11	11	H HD DIGITAL RECEIVER													0	0			0	0
12	13	H SINGLE TUNER DVR													0	0			0	0
13	016- Munjor	H HD DVR													0	0			0	0
14	15	H ADD'L DUAL TUNER DVR													0	0			0	0
15	01A	H WHOLE HOME MR-DVR													0	0			0	0
16	01B	H ARRIS GATEWAY AND MEDIA PLAYER	9	0	0	0	0	0	0	0	0	0	9	9	0	0			0	0
17	01C	H ARRIS ADD'L MEDIA PLAYERS	16	0	0	0	0	0	0	0	0	0	16	16	0	0			0	0
18	01D	H FUSION MYSTREAM	2	0	0	0	0	0	0	0	0	0	2	2	0	0			0	0
19	01J	H FREE CHROMECAST													0	0			0	0
20	20	D EAGLE DIGITAL	3	0	0	0	0	0	0	0	0	0	3	3	0	0			0	0

<colgroup><col><col><col><col span="10"><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

H DIGITAL RECEIVER

<tbody>
</tbody>

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:91.25px;" /><col style="width:233.82px;" /><col style="width:75.09px;" /><col style="width:65.58px;" /><col style="width:75.09px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#b8cce4; "> </td><td style="background-color:#b8cce4; ">Service</td><td style="background-color:#b8cce4; ">Start</td><td style="background-color:#b8cce4; ">Install</td><td style="background-color:#b8cce4; ">Reconnect</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >011 - Victoria</td><td >Service</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td><td >C EXPANDED BASIC</td><td style="text-align:right; ">10</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">2</td><td >D ALL DIGITAL PROGRAMMING</td><td style="text-align:right; ">11</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">3</td><td >C BASIC</td><td style="background-color:#92d050; text-align:right; ">12</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">6</td><td >F COPYRIGHT FEE</td><td style="text-align:right; ">13</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">7</td><td >F FCC USER FEE</td><td style="text-align:right; ">14</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">8</td><td >F RERTRANSMISSION FEE</td><td style="text-align:right; ">15</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">9</td><td >H DIGITAL RECEIVER</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">10</td><td >H ADD'L DIG RECEIVER</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">11</td><td >H HD DIGITAL RECEIVER</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">13</td><td >H SINGLE TUNER DVR</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >016- Munjor</td><td >H HD DVR</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">15</td><td >H ADD'L DUAL TUNER DVR</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">01A</td><td >H WHOLE HOME MR-DVR</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >01B</td><td >H ARRIS GATEWAY AND MEDIA PLAYER</td><td style="text-align:right; ">9</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >01C</td><td >H ARRIS ADD'L MEDIA PLAYERS</td><td style="text-align:right; ">16</td><td style="text-align:right; ">0</td><td style="background-color:#ffff00; text-align:right; ">15</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >01D</td><td >H FUSION MYSTREAM</td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >01J</td><td >H FREE CHROMECAST</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">20</td><td >D EAGLE DIGITAL</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="background-color:#b8cce4; ">Town</td><td style="background-color:#b8cce4; ">Service</td><td style="background-color:#b8cce4; ">Column</td><td style="background-color:#b8cce4; ">Result</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >Munjor</td><td >H ARRIS ADD'L MEDIA PLAYERS</td><td >Reconnect</td><td style="background-color:#ffff00; text-align:right; ">15</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >Victoria</td><td >C BASIC</td><td >Start</td><td style="background-color:#92d050; text-align:right; ">12</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D25</td><td >=VLOOKUP($B25,OFFSET(INDIRECT("B" &MATCH("*"&$A25&"*",$A$1:$A$20,0)),0,0,IFERROR(MATCH("*-*",OFFSET(INDIRECT("A" & MATCH("*"&$A25&"*",$A$1:$A$20,0)+1),0,0,ROW($A$20)),0),ROW($A$20)),15),MATCH($C25,$B$1:$P$1,0),0)</td></tr></table></td></tr></table>

 

[chadlaw32]

I think that will work i just have a question with does the bolded section below do?

=VLOOKUP($B25,OFFSET(INDIRECT("B" &MATCH("*"&$A25&"*",$A$1:$A$20,0)),0,0,IFERROR(MATCH("*-*",OFFSET(INDIRECT("A" & MATCH("*"&$A25&"*",$A$1:$A$20,0)+1),0,0,ROW($A$20)),0),ROW($A$20)),15),MATCH($C25,$B$1:$P$1,0),0)

 

[DanteAmor]

The formula searches for "Munjor" and then searches for "-", if it does not find the "-" then the last row of the range to search for "H ARRIS ADD'L MEDIA PLAYERS" is 20

 

[chadlaw32]

If my sheet end up going down to like 8000 i would need to change that number?

 

[DanteAmor]

You must make several adjustments

=VLOOKUP($B25,OFFSET(INDIRECT("B" &MATCH("*"&$A25&"*",$A$1:$A$8000,0)),0,0,IFERROR(MATCH("*-*",OFFSET(INDIRECT("A" & MATCH("*"&$A25&"*",$A$1:$A$8000,0)+1),0,0,ROW($A$8000)),0),ROW($A$8000)),15),MATCH($C25,$B$1:$P$1,0),0)

A25 by the cell where you are going to put "Munjor"

B25 by the cell where you are going to put "H ARRIS ADD'L MEDIA PLAYERS"

C25 by the cell where you are going to put "Reconnect"

Before making changes to the formula, try my example.