EXCEL LOOKUP PROBLEM

  • Thread starter Asoka Walpitagama - Brandix College IT
  • 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
 
J

Jacob Skaria

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
 
L

Luke M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top