J
JB2010
hi there
i am trying to build a formula that will reference the row & column headings
of a cell ref depending on its contents.
for example, lets say ive got a table like this;
A B C D
1 Jan Feb Mar
2 Sales 100 120 155
3 Costs 80 95 120
4 Total 20 35 35
i am looking to put together what i think will be a merge of LookUp, Index,
Match & Text formulas in an unused cell, E5, to say;
search in cells B24, if any value is over 150, return the text of the
column heading then &" "& then the text of the row heading.
in this instance, cell E5 would therefore say "Mar Sales".
i have seen IndexMatch formulas work the other way (i.e. I specific "Mar" &
"Sales" & it returns the values (155), but never this way round
anyone got any ideas? please let me know if you need any more info
cheers
jb
i am trying to build a formula that will reference the row & column headings
of a cell ref depending on its contents.
for example, lets say ive got a table like this;
A B C D
1 Jan Feb Mar
2 Sales 100 120 155
3 Costs 80 95 120
4 Total 20 35 35
i am looking to put together what i think will be a merge of LookUp, Index,
Match & Text formulas in an unused cell, E5, to say;
search in cells B24, if any value is over 150, return the text of the
column heading then &" "& then the text of the row heading.
in this instance, cell E5 would therefore say "Mar Sales".
i have seen IndexMatch formulas work the other way (i.e. I specific "Mar" &
"Sales" & it returns the values (155), but never this way round
anyone got any ideas? please let me know if you need any more info
cheers
jb