Efficient Tables

[duncs]

im looking for a more efficient way to specify two variables (row and column heading) and then having excel return a value from an array of numbers. presently i am using H&VLOOKUP functions but they seem to put a limit on the number of rows that are allowed in the array.
cheers
duncs

 

[schwarz]

Can you consideruse OFFSET(reference,rows, cols,height,width)function?

GOOD LUCK.

 

[Mutt]

Didn't know there was a limit!
Anyhow, could try :
Match function to establish the row - use FALSE setting if exact match required.
Match function to establish the column number
Then use Address function to get cell address based on row plus constant and column plus constant
Then use indirect function to get contents of cell addressed.
Not sure if there is any limit on the match functions.

 

[Tim401]

I have a speadsheet that looks a table and selects the appropriate value - bit like a bus timetable.

How can I email you a copy through the forum?

 

[duncs]

hi tim401,
that would be great if you could do that, here is my email duncanhundley@hotmail.com
cheers
duncan