Hi Domenic,
Thank you for further assistance. I'm not sure what has happened but I'm
still not getting the Expected Results from either of the current Formulae.
Domenic wrote:
Okay, I think I see the problem, I forgot the +1 bit...
The original notation...
=IF(U111,MATCH(TRUE,F11:INDEX(F11:R11,U11-1)=V11,0)+1,#N/A)
...confirmed with CONTROL+SHIFT+ENTER.
The named ranges...
=IF(U111,MATCH(TRUE,INDEX(NValues2=V11,0),0)+1, #N/A)
or
=IF(U111,MATCH(TRUE,NValues2=V11,0)+1,#N/A)
...confirmed with CONTROL+SHIFT+ENTER.
Based on the Sample Data below: can you see if you get Numeric Label 14
(currently housed in Column Number 16) and its Numeric Value to Return a
Column Number of 14 using the current Formulae.
Sample Data:
Column Number 1 Row 10 Houses Numeric Label 0
Column Number 1 Row 11 Houses Numeric Value 16
Column Number 2 Row 10 Houses Numeric Label 2
Column Number 2 Row 11 Houses Numeric Value 12
Column Number 3 Row 10 Houses Numeric Label 1
Column Number 3 Row 11 Houses Numeric Value 10
Column Number 4 Row 10 Houses Numeric Label 4
Column Number 4 Row 11 Houses Numeric Value 9
Column Number 5 Row 10 Houses Numeric Label 6
Column Number 5 Row 11 Houses Numeric Value 9
Column Number 6 Row 10 Houses Numeric Label 12
Column Number 6 Row 11 Houses Numeric Value 7
Column Number 7 Row 10 Houses Numeric Label 8
Column Number 7 Row 11 Houses Numeric Value 6
Column Number 8 Row 10 Houses Numeric Label 3
Column Number 8 Row 11 Houses Numeric Value 5
Column Number 9 Row 10 Houses Numeric Label 5
Column Number 9 Row 11 Houses Numeric Value 5
Column Number 10 Row 10 Houses Numeric Label 9
Column Number 10 Row 11 Houses Numeric Value 5
Column Number 11 Row 10 Houses Numeric Label 11
Column Number 11 Row 11 Houses Numeric Value 5
Column Number 12 Row 10 Houses Numeric Label 7
Column Number 12 Row 11 Houses Numeric Value 4
Column Number 13 Row 10 Houses Numeric Label 13
Column Number 13 Row 11 Houses Numeric Value 4
Column Number 14 Row 10 Houses Numeric Label 15
Column Number 14 Row 11 Houses Numeric Value 3
Column Number 15 Row 10 Houses Numeric Label 10
Column Number 15 Row 11 Houses Numeric Value 2
Column Number 16 Row 10 Houses Numeric Label 14
Column Number 16 Row 11 Houses Numeric Value 2
Column Number 17 Row 10 Houses Numeric Label 18
Column Number 17 Row 11 Houses Numeric Value 2
Based on the above Sample Data, I would expect Numeric Label 14 to Return
Column Number 14 as the correct Result using the SEARCH order: Descending for
Numeric Value and Ascending for Numeric Label.
Your original A1 style notation Formula provided a Result closer to the
Expected Result and made reference to the COLUMN Function (not sure if
relevant).
The A1 style notation Formula below (still using sample data above) will
Return a Column Number Result of 15 for Numeric Label 14. However, the
correct Result required is Column Number 14 using the SEARCH order:
Descending for Numeric Value and Ascending for Numeric Label.
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)
Cheers,
Sam
Domenic wrote:
Okay, I think I see the problem, I forgot the +1 bit...
The original notation...
=IF(U111,MATCH(TRUE,F11:INDEX(F11:R11,U11-1)=V11,0)+1,#N/A)
...confirmed with CONTROL+SHIFT+ENTER.
The named ranges...
=IF(U111,MATCH(TRUE,INDEX(NValues2=V11,0),0)+1, #N/A)
or
=IF(U111,MATCH(TRUE,NValues2=V11,0)+1,#N/A)
...confirmed with CONTROL+SHIFT+ENTER.
Hi Domenic,
[quoted text clipped - 5 lines]
SEARCH/ sort type for Numeric Labels being Ascending and Numeric Values
Descending.
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1