A sample of the data I'm working with looks like this:

Formations
313** 313**313** 317** 317**317**
TSC
21960000 12.737
21961000 10.259
21962000 11.584 12.860 24.542
21963000 13.155
21964000 14.038
21965000 14.333 15.511 32.200
21967000 11.293 12.762
24207000
I am trying to write a formula which will return the value of, for example,
TSC 21962000 and formation 317**. I 'm sure it's easy but for the life of me
I can't get it to work. Can someone help please?

=INDEX(A1:D11,MATCH(21962000,A1:A10,0),MATCH("317* *",B1:E1,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"yhtak" wrote in message
...
A sample of the data I'm working with looks like this:

Formations
313** 313**313** 317** 317**317**
TSC
21960000 12.737
21961000 10.259
21962000 11.584 12.860 24.542
21963000 13.155
21964000 14.038
21965000 14.333 15.511 32.200
21967000 11.293 12.762
24207000
I am trying to write a formula which will return the value of, for
example,
TSC 21962000 and formation 317**. I 'm sure it's easy but for the life of
me
I can't get it to work. Can someone help please?

I must be doing something wrong, I got a value pf zero.

"yhtak" wrote:

A sample of the data I'm working with looks like this:

Formations
313** 313**313** 317** 317**317**
TSC
21960000 12.737
21961000 10.259
21962000 11.584 12.860 24.542
21963000 13.155
21964000 14.038
21965000 14.333 15.511 32.200
21967000 11.293 12.762
24207000
I am trying to write a formula which will return the value of, for example,
TSC 21962000 and formation 317**. I 'm sure it's easy but for the life of me
I can't get it to work. Can someone help please?

It's difficult to see how your data was laid out, but maybe...

=INDEX(A1:D9,MATCH(21962000,A1:A9,0),MATCH("317~*~ *",A1:D1,0))

If that doesn't help, use the whole range in that first portion of the formula.

then use just column 1 of the range in the first match().

Then use just row 1 of the range in the second match().

And the ~*~* will tell excel to treat the asterisks as asterisks--not wildcards.



yhtak wrote:

I must be doing something wrong, I got a value pf zero.

"yhtak" wrote:

A sample of the data I'm working with looks like this:

Formations
313** 313**313** 317** 317**317**
TSC
21960000 12.737
21961000 10.259
21962000 11.584 12.860 24.542
21963000 13.155
21964000 14.038
21965000 14.333 15.511 32.200
21967000 11.293 12.762
24207000
I am trying to write a formula which will return the value of, for example,
TSC 21962000 and formation 317**. I 'm sure it's easy but for the life of me
I can't get it to work. Can someone help please?



--

Dave Peterson

This is great, thanks very much for your help.

"Dave Peterson" wrote:

It's difficult to see how your data was laid out, but maybe...

=INDEX(A1:D9,MATCH(21962000,A1:A9,0),MATCH("317~*~ *",A1:D1,0))

If that doesn't help, use the whole range in that first portion of the formula.

then use just column 1 of the range in the first match().

Then use just row 1 of the range in the second match().

And the ~*~* will tell excel to treat the asterisks as asterisks--not wildcards.



yhtak wrote:

I must be doing something wrong, I got a value pf zero.

"yhtak" wrote:

A sample of the data I'm working with looks like this:

Formations
313** 313**313** 317** 317**317**
TSC
21960000 12.737
21961000 10.259
21962000 11.584 12.860 24.542
21963000 13.155
21964000 14.038
21965000 14.333 15.511 32.200
21967000 11.293 12.762
24207000
I am trying to write a formula which will return the value of, for example,
TSC 21962000 and formation 317**. I 'm sure it's easy but for the life of me
I can't get it to work. Can someone help please?



--

Dave Peterson