Can INDEX/MATCH functions return multiple rows and columns

[capcon]

Hi,

I'm using a dropdown validation/list function and wondered if it's possible to use the MATCH & INDEX functions to return multiple rows/columns of data. I'm currently using INDEX [ =index((a1:b12,c1:d12,e1:f12,g1:h12),2,2,4) ] to return a single value from those four tables but I'd like to return all the values in the selected table eg in my example here instead of pulling back the value in row 2, column 2, table-4 I would like to return all the values in say table 4?

Will MATCH and INDEX be able to achieve this?

Thanks

 

[capcon]

Further to my post above, and please excuse my lack of knowledge but this is all new to me.

I have a query with the offset function too.

Again, in experimenting to try and return a range of rows/columns I have used the offset as follows and don't understand why I get '0' returned:

=OFFSET(A1,1,0,COUNT(A2:A13),COUNT(B2:B13))

where my table has dates [mmm/yy] in cells A2-A13 and numbers in B1:B13

so all I'm trying to do is return everything in a2:b13

Thanks

 

[NARAYANK991]

Hi ,


Can you be more specific about what you would like to do ?


If you have a formula in one cell , what is your objective in returning a table ? Do you want to directly operate on this table , by summing up all its values , or do you want to use this table to lookup some value ?


Regarding the OFFSET function , the syntax of the OFFSET function is explained here :


http://office.microsoft.com/en-us/excel-help/offset-HP005209208.aspx


In brief , it is : OFFSET(reference,rows,cols,height,width)


Your reference is A1 , rows offset = 1 , column offset = 0 , height ( number of rows ) = COUNT(A2:A13) , which means if all the cells A2 through A13 are having data in them , this will = 12 , width ( number of columns ) = COUNT(B2:B13) , which again , if all the cells B2 through B13 are having data in them , will return 12.


Thus , the above formula returns the range A2:L13 ; however , if you just use this formula on its own , it will not do anything related to the range ; if you use it by putting it within a SUM function as :


=SUM(OFFSET(A1,1,0,COUNT(A2:A13),COUNT(B2:B13)))


you will see that it sums up all the values within the range A2:L13


Similarly , you can have a formula like the following :


=VLOOKUP(7,OFFSET(A1,1,0,COUNT(A2:A13),COUNT(B2:B13)),12,FALSE)


which will return the value corresponding to 7 ( assuming there is a value 7 in column A cells A2 through A13 ) , from column L.


Narayan