Continuation of Formula in New Array

G

Gunjani

Hi
I am using this formula VLOOKUP($B$1,'Team
A'!$A$18:$IV$45,MATCH($B5,'Team A'!$18:$18,0),0)

The Array starts from A18

As you realise due to Excel limitation (!) the array only goes as far
as Column IV; I wish to continue the Table array ( starting from a
new row rather than a new worksheet) say from Cell A50 to IV77 and then
continue again from A82 to IV109 and perhaps etc
Is it possible.

Please Note If it is to be done in Macro Please provide me a detailed
Idiot Guide
as I have NEVER PERFORMED/USED a Macro, and would not know where to
start.

Thx
Gunjani
 
P

Pete_UK

I don't understand why you can't extend the first table from A18:IV45
by another 28 rows so that it covers A18:IV72 to incorporate your
second table and then extend it a further 28 rows to incorporate your
third table, so you will have a contiguous table from A18 to IV100.

Better still, though, why not transpose your data so instead of having
255 columns by 28 rows three times, you make it 28 columns by 765 rows?
You can use Paste Special with Transpose to achive this.

If you insist on having three separate tables with your current
arrangement then you will need to construct a formula along the lines:

IF B5 is in the first table's header row AND B1 is in the first table,
then get the corresponding value
ELSEIF B5 is in the 2nd table's header row AND B1 is in the 2nd table,
then get the corresponding value
ELSEIF B5 is in the 3rd table's header row AND B1 is in the 3rd table,
then get the corresponding value
ELSE not found.

The first part of this would become:

=IF(NOT(ISERROR(VLOOKUP($B$1,'Team A'!$A$18:$IV$45,MATCH($B5,'Team
A'!$18:$18,0),0)),VLOOKUP($B$1,'Team A'!$A$18:$IV$45,MATCH($B5,'Team
A'!$18:$18,0),0),IF(NOT ...

and so on.

Please consider transposing your data and reworking your formulae.

Hope this helps.

Pete
 
Top