EXCEL LOOKUP PROBLEM

  • Thread starter Thread starter Asoka Walpitagama - Brandix College IT
  • Start date Start date
A

Asoka Walpitagama - Brandix College IT

In the following data set I need to fill the 'START DATE' column (F) with the
dates
( in columns B - E ) where the first number appears as shown below

Eg. CODE 1 will have 3-Jan-09 because the first number (600) appears on D
col. where the date is 3-Jan-09

A B C D E
F
1-Jan-09 2-Jan-09 3-Jan-09 4-Jan-09 START DATE
CODE 1 600 700 3-Jan-09
CODE 2 - 400 333 - 2-Jan-09
CODE 3 - - - 232 4-Jan-09
CODE 4 222 - 454 4545 1-Jan-09
CODE 5 - 333 - - 2-Jan-09
CODE 6 - - 444 2323 3-Jan-09
 
In F2 copy the below formula and copy down as required. Please note that this
is an array formula. Within the cell in edit mode (F2) paste this formula and
press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula>}"

=INDEX($B$1:$E$1,MATCH(1,--(B2:E2>0),0))

If this post helps click Yes
 
You can use this array** formula in F2, and then copy down:

=INDEX($B$1:$E$1,1,MATCH(TRUE,ISNUMBER(B2:E2),0))

** Confirm an array formula using Ctrl+Shift+Enter, not just Enter.
 
Back
Top