LOOKUP - Multiples of..

[Read_This]

I have a worksheet that uses a Data Connection to retrieve an array of data. I then have a working passive LOOKUP formula that uses preset array of cells "B76:S76" to retieve the last number in the right of that array;

Cell E2 =LOOKUP(2,1/ISNUMBER(B76:S76),B76:S76)

My problem is that array can have an additional row, or one less - rendering the LOOKUP usless at times unless I amend the formula,

So, I have a formula that uses an active LOOKUP to get to that same array;

Cell F2 =CONCATENATE(TEXT("B"&MATCH($E$1,INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2),0)+MATCH(B3,$A$1:$A$1253,0)-2-6-1,0),":",TEXT("S"&MATCH($E$1,INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2),0)+MATCH(B3,$A$1:$A$1253,0)-2-6-1,0))

Is there a way to combined the second formula into the first LOOKUP ?

iTracker

*	A	B	C	D	E	F
1	*	AREA	*	4 Week Avg	Actual	ROW CONCATENATE
2	*	Highlands & Islands	*	156	62	B76:S76
3	*	North East Scotland	*	159	52	B97:S97
4	*	Central Scotland	*	124	62	B117:S117
5	*	South East Scotland	*	142	57	B137:S137
6	*	West Central Scotland	*	178	78	B156:S156

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 54px;"><col style="width: 146px;"><col style="width: 60px;"><col style="width: 60px;"><col style="width: 60px;"><col style="width: 100px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =VLOOKUP("4 Week Average",A73:S80,19,0) E2 =LOOKUP(2,1/ISNUMBER(B76:S76),B76:S76) F2 =CONCATENATE(TEXT("B"&MATCH($E$1,INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2),0)+MATCH(B3,$A$1:$A$1253,0)-2-6-1,0),":",TEXT("S"&MATCH($E$1,INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B3,$A$1:$A$1253,0)-2),0)+MATCH(B3,$A$1:$A$1253,0)-2-6-1,0)) D3 =VLOOKUP("4 Week Average",A93:S101,19,0) E3 =LOOKUP(2,1/ISNUMBER(iTracker!B97:S97),iTracker!B97:S97) F3 =CONCATENATE(TEXT("B"&MATCH($E$1,INDIRECT("A"&MATCH(B4,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B4,$A$1:$A$1253,0)-2),0)+MATCH(B4,$A$1:$A$1253,0)-2-6-1,0),":",TEXT("S"&MATCH($E$1,INDIRECT("A"&MATCH(B4,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B4,$A$1:$A$1253,0)-2),0)+MATCH(B4,$A$1:$A$1253,0)-2-6-1,0)) D4 =VLOOKUP("4 Week Average",A113:S121,19,0) E4 =LOOKUP(2,1/ISNUMBER(iTracker!B117:S117),iTracker!B117:S117) F4 =CONCATENATE(TEXT("B"&MATCH($E$1,INDIRECT("A"&MATCH(B5,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B5,$A$1:$A$1253,0)-2),0)+MATCH(B5,$A$1:$A$1253,0)-2-6-1,0),":",TEXT("S"&MATCH($E$1,INDIRECT("A"&MATCH(B5,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B5,$A$1:$A$1253,0)-2),0)+MATCH(B5,$A$1:$A$1253,0)-2-6-1,0)) D5 =VLOOKUP("4 Week Average",A133:S141,19,0) E5 =LOOKUP(2,1/ISNUMBER(iTracker!B137:S137),iTracker!B137:S137) F5 =CONCATENATE(TEXT("B"&MATCH($E$1,INDIRECT("A"&MATCH(B6,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B6,$A$1:$A$1253,0)-2),0)+MATCH(B6,$A$1:$A$1253,0)-2-6-1,0),":",TEXT("S"&MATCH($E$1,INDIRECT("A"&MATCH(B6,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B6,$A$1:$A$1253,0)-2),0)+MATCH(B6,$A$1:$A$1253,0)-2-6-1,0)) D6 =VLOOKUP("4 Week Average",A153:S160,19,0) E6 =LOOKUP(2,1/ISNUMBER(iTracker!B156:S156),iTracker!B156:S156) F6 =CONCATENATE(TEXT("B"&MATCH($E$1,INDIRECT("A"&MATCH(B7,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B7,$A$1:$A$1253,0)-2),0)+MATCH(B7,$A$1:$A$1253,0)-2-6-1,0),":",TEXT("S"&MATCH($E$1,INDIRECT("A"&MATCH(B7,$A$1:$A$1253,0)-2-6):INDIRECT("A"&MATCH(B7,$A$1:$A$1253,0)-2),0)+MATCH(B7,$A$1:$A$1253,0)-2-6-1,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Aladin Akyurek]

=LOOKUP(2,1/ISNUMBER(B76:S76),B76:S76)

can be replaced with a faster, more appropriate formula:

=LOOKUP(9.99999999999999E+307,B76:S76)

 

[Read_This]

=LOOKUP(2,1/ISNUMBER(B76:S76),B76:S76)

can be replaced with a faster, more appropriate formula:

=LOOKUP(9.99999999999999E+307,B76:S76)

Many thanks for your prompt reply - I will use it within the formula however, I will still have the problem where I need my second formula (in cell F2) combinbed with that B76:S76 array

 

[Aladin Akyurek]

Many thanks for your prompt reply - I will use it within the formula however, I will still have the problem where I need my second formula (in cell F2) combinbed with that B76:S76 array

Keep them separate...

=LOOKUP(9.99999999999999E+307,INDIRECT($F2))

 

[Read_This]

Keep them separate...

=LOOKUP(9.99999999999999E+307,INDIRECT($F2))

Perfect - Thank you !

 

[Aladin Akyurek]

Perfect - Thank you !

You are welcome.