Hi Domenic,
Your Formula has certainly done the job - thank you very much for all your
help.
I tried to adapt your Formula using a Named Range (NLabels) created for the
Numeric Labels - but used with an Offset of ONE Row to point to the Numeric
Values below the Numeric Labels: cell Ranges F11:R11 (per your Formula).
However, I cannot get the INDEX and OFFSET Functions to return the correct
values.
Is it possible for you to re-vamp the Formula below using the Named Range
NLabels with an Offset of "one" for the Row to replace Cell Ranges F11:R11.
=IF(U111,LOOKUP(2,1/(F11:INDEX(F11:R11,U11-1)=V11),COLUMN(F11:INDEX(F11
:R11,U11-1))-COLUMN(F11)+1)+1,#N/A)
This will search left for the first column containing a value greater
than or equal to V11, return the column position, and add 1. If the
column number representing the numeric label is 1, the formula will
return #N/A since no values exist to the left.
Cheers,
Sam
Domenic wrote:
Try the following...
Let T11 contain your criteria/numerical label
U11:
=MATCH(T11,$F$10:$R$10,0)
This will return the column position.
V11:
=INDEX(F11:R11,U11)+1
This will return the corresponding value in Row 11, and add 1.
W11:
=IF(U111,LOOKUP(2,1/(F11:INDEX(F11:R11,U11-1)=V11),COLUMN(F11:INDEX(F11
:R11,U11-1))-COLUMN(F11)+1)+1,#N/A)
This will search left for the first column containing a value greater
than or equal to V11, return the column position, and add 1. If the
column number representing the numeric label is 1, the formula will
return #N/A since no values exist to the left.
X11:
=IF(U111,IF(ISNA(W11),LOOKUP(2,1/(F11:INDEX(F11:R11,U11-1)=T11),COLUMN(
F11:INDEX(F11:R11,U11-1))-COLUMN(F11)+1)+1,W11),#N/A)
Here, if W11 equals #N/A, the numerical label is used to search left for
the first column containing a value greater than or equal to the numeric
value, return the column position, and add 1. Otherwise, it returns the
value in W11. And, again, if the column number representing the numeric
label is 1, the formula will return #N/A since no values exist to the
left.
Post back if I misinterpreted your intent...
Hope this helps!
Hi All,
[quoted text clipped - 41 lines]
Thanks
Sam
--
Message posted via
http://www.officekb.com