Return data at the intersection of a row and column

[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

 

[JulieD]

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,B11,0))

Hope this helps
Cheers
JulieD

 

[Alan Beban]

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?

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

 

[Charles Williams]

INDEX would be more efficient

=INDEX(Table,RowNum,ColumnNum)


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

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,B11,0))

Hope this helps
Cheers
JulieD

 

[JulieD]

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

 

[Frank Kabel]

Hi Julie
I would use INDEX: Reason: not a volatile function as OFFSET. So use:
=INDEX(A14,MATCH(A11,A1:A4,0),MATCH(A10,A11,0))

 

[Frank Kabel]

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

 

[JulieD]

Frank, thanks for this - i'll check out the url when my i'net next working
....

Julie