Help on Excel Array

[jupops]

Good Evening

Can anybody help.

I am trying to write an Array formula that if a number is true will display a cells result in a cell, so an example is:

My data the Array will be looking at is on Details tab :

zone 1 zone 2 zone 3 zone 4 zone 5 zone 6 zone 7 zone 8 zone 9 zone 10
135 138 43 47 21 33 41
5 6 456 12 13 21 455 23 42 43

And the Array will be in a table where I have a static title list (5.6.11.12…..) which if the number corresponds to the source data will put the letter from the first cell (M or X) into the list

5 6 11 12 13 21 22 23 31 32 33 36 37 41
M M M M
X X X X X X X

The formulas I have are:

B2 formula is {=IF(Details!F2:Z2=5,Summary!A2,"")}
C2 formula is {=IF(Details!F2:Z2=6,Summary!A2,"")}

B3 formula is {=IF(Details!F3:Z3=5,Summary!A3,"")}
C3 formula is {=IF(Details!F3:Z3=6,Summary!A3,"")}

Which don’t seem to work, can you suggest where I have gone wrong.

Thank you for your help in advance

Regards

Jupops

 

[SkipVought]

Hi,

Totally incomprehensible!

Your EXAMPLE does not correspond to the formulas that you post -- You have 10 columns & 14 columns in your examples and 21 in the RANGE reference.

Please be clear, concise and complete. Explain everything in painful detail, because no one can see what is in your head unless your reveal it.

My data the Array will be looking at is on Details tab :

And the Array will be in a table where I have a static title list (5.6.11.12.....)

Maybe the MATCH function would work...
[tt]
=if(isna(match(5,Details!F2:Z2,0)),"",Summary!A2)
=if(isna(match(6,Details!F2:Z2,0)),"",Summary!A2)

=if(isna(match(5,Details!F3:Z3,0)),"",Summary!A3)
=if(isna(match(6,Details!F3:Z3,0)),"",Summary!A3)
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Not an expert on array formulae but I thought the formulae in each cell had to be identical, entered into the entire range at the same time using Ctrl-Shift-Enter.

Calculate multiple results

Some worksheet functions return arrays of values, or require an array of values as an argument. To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments have.
1. Select the range of cells in which you want to enter the array formula.
2. Type the array formula.
3. Press CTRL+SHIFT+ENTER.

I guess you have entered the formulae individually so you have multple different array formulae each of which fail to follow the first part of the above guidance.


There may be a more elegant way but using your original logic:
In C2 use =IF(Details!F2=C$1,Summary!$A2,"")
Copy and paste to the other cells.

Scrub that solution - I've just learnt something about arrays! Highlight your results cells and use
=IF(Details!F2:z3=C1:z1,A2:A3,"") Ctrl-Shift-Enter

Gavin

 

[jupops]

HI Ya

I can only say I am very sorry about my original question, it was late and my mind was completely mushed.

You managed to decipher my requirement form the garbage I wrote and the match formula works a treat.

Thank you very much for your help


Regards

Jupops