What would you want if two (or more) cells meet your condition.


I think you will need VBA for this as the data will be random i.e. not
sorted ascending or descending order so MATCH (or "LOOKUPS") etc not would
work on "nearest" value searches.

"JB2010" wrote:

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 B2:D4, 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

Hi


yeah, really i do want a limitless listing going off down the page of all
those refs that are over 150. i just thought it was easier to start this
thread with just saying one cell returned!


i cant begin to imagine how i would write the code to do something like that?


jb

"Toppers" wrote:

What would you want if two (or more) cells meet your condition.


I think you will need VBA for this as the data will be random i.e. not
sorted ascending or descending order so MATCH (or "LOOKUPS") etc not would
work on "nearest" value searches.