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
<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>
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
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 | ||||||||||||||||||||||||||||||||
<tbody> </tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4