Hi Sam!

No apologies necessary. It looks like we're not in the same
wavelength... :)

When I gave you some sample criteria, along with what I thought would be
the correct result, you confirmed them to be correct. But, according to
your last example, this would not be the case.

I provided the following example where the first six columns had the
following Numeric Labels and corresponding Numeric Values...

0 2 1 4 6 12
16 12 10 10 10 7

....and where the criteria and results were as follows...

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

1 ---------- Column 3

4 ---------- Column 4

6 ---------- Column 5

But according to your last post, the correct results should be...

Criteria ---------- Should Be

1 ---------- Column 3

4 ---------- Column 3

6 ---------- Column 3

Is this correct? 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

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

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

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

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

Are these correct? Now, let's replace 5 with 14, and 12 with 5. If the
criteria is 5, is Column 7 the correct answer? And lastly, if the
criteria is 0, what should the correct answer be?

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

Hi Domenic,

First-off: apologies for re-opening Thread.

Your Formulae provided below works but there are some exceptions where it
does not provide the Expected Results.

Searching for Numeric Label 10 and returning the Column Number of its
corresponding New Numeric Value based on the agreed method for searching and
increasing its original Numeric Value by 1 (one) should Return Column Number
6 as the correct result . Using the Layout and Sample Data below it Returns
Column Number 10.

I appreciate any further help that you may be able to provide.

Example Layout:
Row10 Numeric Labels Column Number 1("F") to Column Number 17("V")
Row11 Numeric Values Column Number 1 ("F") to Column number 17("V")

Sample Data:
Row10 Col1 = 0
Row11 Col1 = 13
Row10 Col2 = 1
Row11 Col2 = 12
Row10 Col3 = 2
Row11 Col3 = 11
Row10 Col4 = 5
Row11 Col4 = 11
Row10 Col5 = 3
Row11 Col5 = 8
Row10 Col6 = 4
Row11 Col6 = 6
Row10 Col7 = 6
Row11 Col7 = 6
Row10 Col8 = 8
Row11 Col8 = 6
Row10 Col9 = 9
Row11 Col9 = 6
Row10 Col10 = 10
Row11 Col10 = 6
Row10 Col11 = 12
Row11 Col11 = 15
Row10 Col12 = 7
Row11 Col12 = 4
Row10 Col13 = 11
Row11 Col13 = 3
Row10 Col14 = 13
Row11 Col14 = 2
Row10 Col15 = 15
Row11 Col15 = 2
Row10 Col16 = 21
Row11 Col16 = 2
Row10 Col17 = 31
Row11 Col17 = 2

Thanks
sam


Domenic wrote:
Assumptions:

F10:FV10 contains the Numeric Labels

F11:V11 contains the Numeric Values

X11 contains the criteria

Formulas:

First, define the following names...

Select Y11

Insert Define Name

Name: NLabels

Refers to: =Sheet1!$F$10:$V$10

Click Add

Name: NNV

Refers to: =INDEX(NValues,Pos)+1

Click Add

Name: NValues

Refers to: =Sheet1!$F11:$V11

Click Add

Name: Pos

Refers to: =MATCH(Sheet1!$X11,NLabels,0)

Click Add

Name: SubRange

Refers to:

=INDEX(NLabels,MATCH(NNV,NValues,0)):INDEX(NLabel s,MATCH(2,1/(NValues=NNV
)))

Click Ok

*Change the sheet reference accordingly.

Then, enter the following formula in Y11, and copy down if necessary...

=IF(ISNUMBER(MATCH(NNV,NValues,0)),(MAX(IF(SubRan ge<$X11,COLUMN(SubRange)
-MIN(COLUMN(SubRange))+1))+1)+(MATCH(NNV,NValues,0)-1),MATCH($X11,NLabels
,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Extraction of previous Data Sample:

[quoted text clipped - 64 lines]
Cheers,
Sam