Excel worksheet link search and fill formulas?

Oh, and i forgot to mention I do not want a VBA solution, spreadsheet must be macro free!

Have a look at INDEX worksheet function
Best wishes
Sreedhar

Thanks for that, however its it possible to build an array formula with the ROW & COLUMN fuctions inserted into the INDEX function?

Im guessing....

={INDEX(A1:C10, (ROW("Occupation")), (COLUMN("CAR")))}

?

Sorry....

Ah now that would be great if i already knew the column numbers that the information appears within, as mentioned its variable so i cant be column specific, or can MATCH be used on an array as apposed to a specific column?

I need an INDEX array that searches the whole sheet for certain requirements and returns the intersecting value.

And being honest, it would be even better if i could have a multiple query to the row number so, for example, 2 requirements must be met i.e. "origin" & "destination". And then column query is maybe "rate"

Hey Ratcat!

Ok, i'll get a simplified version over to you of what i want to do in a bit, however description of requirements are as below:

I have a central spreadsheet/matrix that i operate manually at present, it consists of vast amounts of data comprised by manually inputting information from other spredsheets, creating a very large condensed version of all of them...(with me so far?)

What i want to do is set up an 'auto updater' version of the matrix by programming each cell on the matrix to look within specific source files to return their corresponding values.

However, the information sources are all in varying formats and they often change their overall format on every update. Considering this to be the case I cannot use direct cell links only (of which would of been ideal if the formats didnt change)

What i need is a generic search formula that i can taylor to each cell link on my matrix to look for specific requirements on the other spreadsheets, and return the corresponding value.

So in example, say i need to know a postage rate from one country to another, i need to find the row in the source spreadsheet that satisfys both the origin and the destination, and then return the intersecting column which gives the rate for a certain size...

Ive looked at the INDEX function and that looks like it could work, all I need is a clever way of generating the row and column numbers without knowing what they are....(i know) by succesfully matching the search criteria (i.e. origin & destination match = Row6. Postage Rate = Column10)

Also, if possible, i need the search function to be non case sensitive, and be able to look for partial matches i.e. a search for 'England' may be 'eng'.

I know its a little complicated, but if i can get this to work, all i will need to do to update the matrix is purley change the source files through excel links. And at present that would be a god send!!

Oh, and the prob is i run this from a works machine, so no macros are aloud, i have to work within the powers of normal excel formula functionality!

I have this feeling that i know it can happen, its just i am quite rusty on excel array formulas, in words i kinda want it to:

=SEARCH(SPECIFIED SPREADSHEET) FOR (("SYDNEY" & "LONDON") = ROW6) + (("POSTAGE RATE") = COLUMN10) = INTERSECTING VALUE

Is it really that complicated!?!

HELP!

Thanks for all ur efforts ratcat,

I know, its a headache!

Ive been searching through this forum and there are some members that have pieced together some of the most complicated array formulas i have ever seen! I wonder if they can help...

If you are an array formula legend please help!