Return SEARCHED Column Number of Numeric Label and Value

[Domenic]

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[/QUOTE]

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

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?

 

[Sam via OfficeKB.com]

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
[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.

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?

 

[Domenic]

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(SubRange<X11,COLUMN(SubRange)-
MIN(COLUMN(SubRange))+1))+1)+(MATCH(NNV,NValues,0)-1),MAX(IF(NValues>NNV,
COLUMN(NValues)-MIN(COLUMN(NValues))+1)+1))

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

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

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

Cheers,
Sam

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(SubRange<X11,COLUMN(SubRange)-
MIN(COLUMN(SubRange))+1))+1)+(MATCH(NNV,NValues,0)-1),MAX(IF(NValues>NNV,
COLUMN(NValues)-MIN(COLUMN(NValues))+1)+1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic,

[quoted text clipped - 6 lines]

Cheers,
Sam