VLOOKUP Help Please

[Chrazyman]

Alright, I give up. I will come to the masters here. I am trying to copy the times from column D where there is a "0" in column E and transfer those to column A in another tab. Then I would like to copy the times in column D where there is a "1" in column E and transfer those to Column B in another tab as in the pictures below. I appreciate your help.

Capture.PNG CaptureB.PNG

[mikeTRON]

Post a sample workbook, not pictures.

Are you familiar with PivotTables? You could just wrap a named range around the DATA then filter on column E to be 0 and it will pull those dates in...

[Tony Valko]

See if this is what you had in mind:

http://www.excelforum.com/tips-and-t...ml#post3376007

[sktneer]

Assuming Sheet1 contains your data with dates in col. D (starting from row4) and numbers 0 or 1 in col. E (starting from row 4), then on sheet2.......

Col. A in cell A2 , place this array formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down Ctrl+Shift and then press Enter) and drag down the formula until you get blank cells.

Please Login or Register  to view this content.

Col. B in cell B2 , place this array formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down Ctrl+Shift and then press Enter) and drag down the formula until you get blank cells.

Please Login or Register  to view this content.

Is this what you want?

[Tony Valko]

Using ROW(A1) leaves the formula(s) vulnerable to new row insertions on the sheet that contains the formula(s).

Insert a new row 1 and ROW(A1) becomes ROW(A2) and now the formula will not return the correct first result.

Using ROWS(...) is more robust.

ROWS(A$1:A1)

Insert a new row 1 and ROWS(A$1:A1) becomes ROWS(A$2:A2) and still evaluates to 1.

[Chrazyman]

I took sktneer's formula and added Tony's help at the end and it works like a charm. I appreciate everyone's help.

[Tony Valko]

The formula at the link I posted already had the ROWS function incorporated!

Glad you got it working. We appreciate the feedback.


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.