Excel 2007 : Some sort of lookup on a cell into an array of data???

[benoj2005]

Hi

See attached example - lookup using input cell into array.xlsx

Basically need to enter a month and then a formula return the figures from the appropriate column in the data sheet

Thanks

SEE POST #5 for solution

[NBVC]

If the AccNumber sequence is the same.. then:

=INDEX(Data!$B$2:$M$5,ROWS($A$1:$A1),MATCH($C$2,Data!$B$1:$M$1))

else

=INDEX(Data!$B$2:$M$5,ROWS($A$1:$A2),MATCH($C$2,Data!$B$1:$M$1))

copied down

[benoj2005]

If the AccNumber sequence is the same.. then:

=INDEX(Data!$B$2:$M$5,ROWS($A$1:$A1),MATCH($C$2,Data!$B$1:$M$1))

else

=INDEX(Data!$B$2:$M$5,ROWS($A$1:$A2),MATCH($C$2,Data!$B$1:$M$1))

copied down

Sequence can be in any order. I have copied your second formula and it dosent work??

[NBVC]

Here it is...

[benoj2005]

Here it is...

No that still dosent work, if I change any of the Acc Numbers in "Return" sheet then the figure does not change accordingly

I have found the solution, see here - lookup using input cell into array.xlsx (If you change any of the account numbers in mine the figure changes accordingly

Thanks for showing me the match function

[NBVC]

I am sorry, I pasted the same formula twice in Post # 2 and in the attachment I had the correct formula in C5, but didn't copy it down.

The formula in C5 is:

=INDEX(Data!$B$2:$M$5,MATCH(B5,Data!$A$2:$A$5,0),MATCH($C$2,Data!$B$1:$M$1))

and copied down to give same results as your VLOOKUP.

... that was careless of me... I apologize.

[benoj2005]

I am sorry, I pasted the same formula twice in Post # 2 and in the attachment I had the correct formula in C5, but didn't copy it down.

The formula in C5 is:

=INDEX(Data!$B$2:$M$5,MATCH(B5,Data!$A$2:$A$5,0),MATCH($C$2,Data!$B$1:$M$1))

and copied down to give same results as your VLOOKUP.

... that was careless of me... I apologize.

Ahh I see, I think mines a little less complicated, maybe because i'm used to vlookups.

You opened my eyes to the match function so thanks anyway

[NBVC]

Where there is a will there are many ways