Return data at the intersection of a row and column

  • Thread starter Thread starter Camien
  • Start date Start date
C

Camien

Hi,

I'm trying to make a reference to a chart using 2 pick lists that allo
you to choose the column and row using those two lists and I nee
something that will return the value found at the intersection of thos
2 points, but I have no idea how to do that.

Any Ideas
 
Hi Camien

one way is to use the OFFSET function & MATCH Functions

eg.
A B C D
1 Jan Feb Mar
2 Hats 10 5 3
3 Shoes 5 2 1
4 Gloves 20 1 5

in A10 i choose the month
in A11 i choose the item
my formula in A12 would be
=OFFSET(A1,MATCH(A11,A2:A4,0),MATCH(A10,B1:D1,0))

Hope this helps
Cheers
JulieD
 
Camien said:
Hi,

I'm trying to make a reference to a chart using 2 pick lists that allow
you to choose the column and row using those two lists and I need
something that will return the value found at the intersection of those
2 points, but I have no idea how to do that.

Any Ideas?
Click to expand...
If your row and column headers are such that they can be used as valid
names for ranges, then select your data range including the headers,
click on Insert|Name|Create, check Top row and Left column, then OK.
After that =rowHeader3 columnheader2 or =columnheader2 rowheader3 will
return the value at the intersection.

Alan Beban
 
INDEX would be more efficient

=INDEX(Table,RowNum,ColumnNum)


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

JulieD said:
Hi Camien

one way is to use the OFFSET function & MATCH Functions

eg.
A B C D
1 Jan Feb Mar
2 Hats 10 5 3
3 Shoes 5 2 1
4 Gloves 20 1 5

in A10 i choose the month
in A11 i choose the item
my formula in A12 would be
=OFFSET(A1,MATCH(A11,A2:A4,0),MATCH(A10,B1:D1,0))

Hope this helps
Cheers
JulieD
Click to expand...
 
Hi Frank

would you mind explaining "why" in more detail - i use the OFFSET function
quite a lot mainly as i can never seem to remember to use the INDEX one ..

Thanks
JulieD
 
Hi Julie
I would use INDEX: Reason: not a volatile function as OFFSET. So use:
=INDEX(A1:D4,MATCH(A11,A1:A4,0),MATCH(A10,A1:D1,0))
 
Hi
OFFSET is a volatile function. that means the formula is re-calculated
each time a value in your sheet changes. If you use a lot of these
formulas this would probably slow down your spreadsheet.
INDEX on the opposite is not volatile (though I think some help
versions state it is one). Benefit: This formula is only re-calculated
if one of the values within its parameters change.

For more about volatile functions see:
http://www.decisionmodels.com/calcsecretsi.htm
 
Frank, thanks for this - i'll check out the url when my i'net next working
....

Julie
 
Back
Top