Re-post

Ian,

Before discussing that long-winded formula, I want to ask you a favor which follows.

Select an unused cell, type =, select A1:M27 (including labels), and press control+shift+enter. Go to the formula bar and hit F9. Copy the array and post it along with the values of P15 and P16.

Aladin

==========

I could but for two reasons

1) I'm not at work

2) someone at work said "is it possible to return the value in row 1 from a ref in col A: and an number which will be in it" I said should be:

the thing is i went away and made the quickest table i could col a2:a27 1;2;3 etc. so i could autofill and b2:m2 jan;feb; etc same reason quickness

i then just put random numbers in the data area.

so if i did this it would just be a random array you'd get and not really that important.

it's the idea that it CAN be done that is driving me crazy. as i've said i just NEED to know then throw it away. you must have done some crazy things just because you don't want them to beat you, even long after you've got a far better answer.

as for what's in p15 + p16

I just typed 15 and 160 (a number i know is in that row)

the formula (long as it is) returns $E$5

Ian

also the F9 thing: is there a way to put this back into individual cells if you don't know the size of the array, terminology's way off by this i mean if i have jan - dec

={"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"}

i know that if i put this in a cell highlight the next 11 in that row and ctrl+shift+enter they go in to the cells required, a simple copy /paste special values.

but if i don't know the exact size can this be done.

Also: how would you have tackled the question i got from a work mate.

That's good enough. I've already cooked up a similarly filled-up array.

However, I've one more question: the long formula has as range: A1:M27.

Now, you say: "made the quickest table i could col a2:a27 1;2;3 etc. so i could autofill and b2:m2 jan;feb; etc "

Should B2:M2 not be B1:M1 that contains labels?

Correct...a gold star!

Re: Correct...a gold star!

Your original post says: "p15 has the row start match".

What is in P15: a row number from the range A1:A27?

Re: Correct...a gold star!

my original post is a bit sketchy:

the references could be in any cells: just happened to be in that cell at the time

=ADDRESS(ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1)),COLUMN(INDEX(A1:M27,ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1)),MATCH(P16,23:23,0))))

p15 is where i typed the row number, could be "cars" or my own personal favourite fruit! a2 apples etc.
so in p15 i could type 22 (only then does the above formula work as it WILL be in row 23:23)

p16 is a number i know is in that row.

Ian

Discussion & Solution

Ok, Ian, here we go.

How unnecessary, I'll reproduce the data in what follows in 2 parts.

Part 1 [ from the range: A1:G27, where A1 is blank. Any 0 is a real, numeric 0. ]

{"","Jan","Feb","Mar","Apr","May","Jun";"john",67,6,91,72,49,86;"ivan",1,6,78,17,63,99;"mark",6,64,73,34,35,67;"laura",3,75,91,2,88,15;"olga",16,29,96,7,69,7;"timur",57,75,1,30,35,96;"ian",70,87,99,23,79,39;"damon",0,12,86,33,43,57;"aladin",20,56,95,12,94,56;"dawn",76,71,20,5,18,8;"daphne",52,37,2,1,96,38;"anais",47,31,24,5,20,54;"edmund",54,0,48,75,3,5;"carl",42,94,93,99,10,44;"zora",91,4,41,27,54,3;"goku",49,62,54,88,50,76;"kakarot",19,9,54,63,75,52;"vegeta",37,96,33,57,43,97;"gohan",98,48,55,86,86,30;"bulma",43,78,39,66,55,2;"piccolo",20,97,21,32,16,7;"tien",33,38,69,2,36,28;"yamtcha",68,85,74,27,71,35;"niel",36,20,91,1,45,2;"krillin",22,35,50,21,33,94;"marron",48,29,93,41,0,40}

Part 2 [ from the range: H1:M27, where any 0 is a real, numeric 0. ]

{"Jul","Aug","Sep","Oct","Nov","Dec";32,22,77,0,21,25;74,35,11,68,23,64;66,14,61,18,17,45;4,96,94,75,50,31;52,72,97,92,15,42;4,36,51,65,28,90;48,46,36,4,52,70;41,69,78,93,64,58;20,2,33,81,64,87;66,49,25,8,30,76;87,23,34,50,85,42;78,64,88,27,34,80;52,65,39,92,78,80;30,42,37,12,88,75;91,37,1,49,84,53;64,21,91,86,74,55;16,66,95,32,13,32;25,31,14,9,31,25;72,17,55,61,73,17;28,86,12,10,12,45;55,8,44,78,6,59;30,22,77,98,98,28;16,38,22,91,74,87;87,24,22,63,40,46;76,4,39,96,83,48;91,94,9,77,0,76}

Let

P15 == anais [ meaning: P15 contains the text value "anais". ]

P16 == 24 [ meaning: P15 contains the text value "anais". ]

Query: In which column is 24, given the value "anais" which is (possibly) in column A in the same row?

In P1 enter: =MATCH(P15,A1:A27,0)

This formula computes/determines the number of the row where "anais" (or P15) is located.

In P3 enter: =MATCH(P16,INDIRECT(P1&":"&P1),0)

If you don't mind computing twice,

in P4 enter: =MATCH(P16,INDIRECT(MATCH(P15,A1:A27,0)&":"&MATCH(P15,A1:A27,0)),0)

The formula in P3 will produce the number of the column where the first occurrence of 24 (if any exists) is located.

The latter formula produces 4 as result, given the data I reproduced at the beginning.

I omit possible checks regarding the formula of P3. Anyway, if either P15 or P16 (or both) do(es) not exist(s), you'll get an error value.

Of course:

=IF(ISNUMBER(P3),INDIRECT(ADDRESS(1,P3)),"Error: Value not found")

is what you are looking for.

Now, that long formula that kept bothering you:

=ADDRESS(ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1)),COLUMN(INDEX(A1:M27,ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1)),MATCH(P16,23:23,0))))

What does this deliver? The last MATCH must first manually be changed to MATCH(P16,13:13,0) because "anais" is in row 13. When so changed, it gives $D$13, which is the cell address where the value 24 is. This is not the label/column heading you're after. And, more importantly, it's way off what you intended to compute. In your search for a formula, you discovered that you needed to construct a lookup range for MATCH that consists of 'N:N" where N is a row number.

Some observations:

ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1))

is redundant. MATCH already delivers the row number (position of the value "anais" in the range A1:A27, so ROW(INDEX... computes that row number again...

COLUMN(INDEX(... has the same type of redundancy...

As long as the last MATCH is not fed by an automatically constructed 'N:N' type of range, this formula will never work. My description above provides that automatic computation.

So I'd suggest you "throw it away."

You told me: "someone at work said "is it possible to return the value in row 1 from a ref in col A: and an number which will be in it"."

And asked:

"Also: how would you have tackled the question i got from a work mate."

I think I answered this one.

You noted that:

also the F9 thing: is there a way to put this back into individual cells if you don't know the size of the array, terminology's way off by this i mean if i have jan - dec

={"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"}

i know that if i put this in a cell highlight the next 11 in that row and ctrl+shift+enter they go in to the cells required, a simple copy /paste special values.

but if i don't know the exact size can this be done.

I don't believe it can be done without the exact size. You can always choose a range much larger than you suspect then delete afterward any columns and/or rows that contain solely #N/A's.

Regards,

Aladin

Re: Discussion & Solution

Phew! thanks

I've settled with:

=IF(ISNUMBER(P3),INDIRECT(ADDRESS(1,MATCH(P16,INDIRECT(MATCH(P15,A1:A27,0)&":"&MATCH(P15,A1:A27,0)),0))),"Error: Value not found")

as i just had to have the 1 formula

as i've said it was a personal nightmare (easy once you know)

my down fall has been not having a grasp of INDIRECT(), I tried every combination of "p15"&: blah blah blah

i was trying stupid things p15&~}>(*#@&":" (not that bad, but close) :)

I'll now stick it in my file of files (the one i keep at work with tons of formulas (most from this board (i have done 1 or 2 myself))) and forget about it forever. I know I sleep better tonight.

don't know if you keep such a workbook, i find it amazing to go back to and realise how much my ideas change from day to day, with just the smallest increase in knowledge)

one thing is obivious to me, that i need to eat more excel spinach if i'm ever gonna know just a small amount of the things that people on this board know. going back to an earlier discussion, more mathematics knowledge is needed also.

Again thanks for the help, you've done it again,

perhaps you'll answer 1 more question

Do you ever sleep??? :)

Ian

P.S. did you ever get an answer to if we can get the missing links back?? i like many others had an answer i was gonna pick up at work and zip/nothing.

Re: Discussion & Solution

=IF(ISNUMBER(P3),INDIRECT(ADDRESS(1,MATCH(P16,INDIRECT(MATCH(P15,A1:A27,0)&":"&MATCH(P15,A1:A27,0)),0))),"Error: Value not found")

Then it is just:
=INDIRECT(ADDRESS(1,MATCH(P16,INDIRECT(MATCH(P15,A1:A27,0)&":"&MATCH(P15,A1:A27,0)),0))),

since P3 does not have anything to test.

I keep (most) workbooks involving questions to which I reply. It causes too much administration though...

Don't worry, I do. :)

Alas.

Aladin

That is what I settled for ...but at 2:00am monday morn who's gonna worry