Return SEARCHED Column Number of Numeric Label and Value

In article 5b18632390ed1@uwe, "Sam via OfficeKB.com" u4102@uwe
wrote:

And just to be sure, let's go through a few more examples using the values
in your last post...

Criteria ---------- Result

1 ---------- Column 14 Should be Column 2

7 ---------- Column 11

4 ---------- Column 6

3 ---------- Column 5

12 ---------- Column 11

Are these correct?

Almost, Numeric Label 1(one) should be Column 2

I'm not sure where I got Column 14. I meant to say Column 2. So far,
so good.

Now, let's replace 5 with 14, and 12 with 5.
If the criteria is 5, is Column 7 the correct answer?

Not sure what you mean?

Replace the Numeric Label 5 with Numeric Label 14, and replace Numeric
Label 12 with Numeric Label 5. If the criteria/Numeric Label is 5, is
Column 7 your expected result?

Hi Domenic,

Replace the Numeric Label 5 with Numeric Label 14, and replace Numeric
Label 12 with Numeric Label 5. If the criteria/Numeric Label is 5, is
Column 7 your expected result?

Yes

Cheers,
Sam

Domenic wrote:
And just to be sure, let's go through a few more examples using the values
in your last post...
[quoted text clipped - 14 lines]

Almost, Numeric Label 1(one) should be Column 2

I'm not sure where I got Column 14. I meant to say Column 2. So far,
so good.

Now, let's replace 5 with 14, and 12 with 5.
If the criteria is 5, is Column 7 the correct answer?

Not sure what you mean?

Replace the Numeric Label 5 with Numeric Label 14, and replace Numeric
Label 12 with Numeric Label 5. If the criteria/Numeric Label is 5, is
Column 7 your expected result?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1

Assuming that the Numerical Labels are unique, and that X11 contains the
criterion, try the following...

1) Change the reference for the defined name 'Pos' to...

=MATCH(Sheet1!$X$11,NLabels,0)

For some reason you had Sheet!$X$10 as your cell reference in the sample
file you sent me.

2) Use the following formula for Y11...

=IF(ISNUMBER(MATCH(NNV,NValues,0)),(MAX(IF(SubRang eX11,COLUMN(SubRange)-
MIN(COLUMN(SubRange))+1))+1)+(MATCH(NNV,NValues,0)-1),MAX(IF(NValuesNNV,
COLUMN(NValues)-MIN(COLUMN(NValues))+1)+1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article 5b1991d4d7bd9@uwe, "Sam via OfficeKB.com" u4102@uwe
wrote:

Hi Domenic,

Replace the Numeric Label 5 with Numeric Label 14, and replace Numeric
Label 12 with Numeric Label 5. If the criteria/Numeric Label is 5, is
Column 7 your expected result?

Yes

Cheers,
Sam

Hi Domenic,

Thank you for all your time and patience. Thank you once again for a solution
- a Great Formula!

Cheers,
Sam

Domenic wrote:
Assuming that the Numerical Labels are unique, and that X11 contains the
criterion, try the following...

1) Change the reference for the defined name 'Pos' to...

=MATCH(Sheet1!$X$11,NLabels,0)

For some reason you had Sheet!$X$10 as your cell reference in the sample
file you sent me.

2) Use the following formula for Y11...

=IF(ISNUMBER(MATCH(NNV,NValues,0)),(MAX(IF(SubRan geX11,COLUMN(SubRange)-
MIN(COLUMN(SubRange))+1))+1)+(MATCH(NNV,NValues,0 )-1),MAX(IF(NValuesNNV,
COLUMN(NValues)-MIN(COLUMN(NValues))+1)+1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic,

[quoted text clipped - 6 lines]
Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1