Vlookup and few more questions :)
 

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

Hi!

Try these:

#1:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(D2:D6,MATCH(TRUE,MMULT(--(A2:C6=G2),TRANSPOSE(COLUMN(A2:C6)^0))0,0))

Where G2 is your lookup number = 32

Format the cell as DATE.

#2:

For dates current date (today):

=SUMPRODUCT(--((LEFT(A2:A6)="1")+(LEFT(B2:B6)="1")+(LEFT(C2:C6)= "1")0),--(D2:D6TODAY()))

For dates < current date (today):

=SUMPRODUCT(--((LEFT(A2:A6)="1")+(LEFT(B2:B6)="1")+(LEFT(C2:C6)= "1")0),--(D2:D6<TODAY()))

Biff

wrote in message
oups.com...
Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

Assuming your table is in A1:D5, try

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
entered using Cntrl+Shift+Enter

and
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

assuming column D are dates and not text.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

One other thing, in my Sumproduct formulas, cell A9 has a 1 in it. Modify to
suit.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

Where B8 = 32 in the Index function and cell A9 has a 1 in it for sumproduct.

"JMB" wrote:

Assuming your table is in A1:D5, try

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
entered using Cntrl+Shift+Enter

and
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

assuming column D are dates and not text.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))

I sure went with overkill on that one!

=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

Those count every instance of 1. I think the OP wanted just the cells that
start with 1.

Biff

"JMB" wrote in message
...
Assuming your table is in A1:D5, try

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
entered using Cntrl+Shift+Enter

and
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

assuming column D are dates and not text.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

Sorry - I misunderstood. You want the cells where the first number =1 and
the dates are less than/greater than the current date
=SUMPRODUCT((--LEFT(A1:C5)=A9)*(D1:D5<TODAY()))
=SUMPRODUCT((--LEFT(A1:C5)=A9)*(D1:D5TODAY()))

Where A9=1. My apologies for the confusion.

"JMB" wrote:

Assuming your table is in A1:D5, try

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
entered using Cntrl+Shift+Enter

and
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

assuming column D are dates and not text.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

I reread the OP's post more carefully (after reading your post). You're
right, he clearly said he wanted to check the first number.

"Biff" wrote:

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))

I sure went with overkill on that one!

=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

Those count every instance of 1. I think the OP wanted just the cells that
start with 1.

Biff

"JMB" wrote in message
...
Assuming your table is in A1:D5, try

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
entered using Cntrl+Shift+Enter

and
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

assuming column D are dates and not text.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

JMB,

<< =INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
<<entered using Cntrl+Shift+Enter

We need to check for entries not found in the array. For example, if I key in 77, I want to see a blank or N/A.

Thank you for your attention.

Epinn

"JMB" wrote in message ...
Assuming your table is in A1:D5, try

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
entered using Cntrl+Shift+Enter

and
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

assuming column D are dates and not text.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you

Vlookup and few more questions :)
 

I overlooked the fact that if there are no matches, MIN returns 0 - so INDEX
will return the entire array, D1:D5. I guess I assumed MIN would return an
error if all of the data fed to it was text.

Try:
=IF(SUM(--(A1:C5=B8)),INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5) ,""))),"N/A")
also array entered

My apologies for the oversight.

"Epinn" wrote:

JMB,

<< =INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
<<entered using Cntrl+Shift+Enter

We need to check for entries not found in the array. For example, if I key in 77, I want to see a blank or N/A.

Thank you for your attention.

Epinn

"JMB" wrote in message ...
Assuming your table is in A1:D5, try

=INDEX(D1:D5,MIN(IF(A1:C5=B8,ROW(A1:A5),"")))
entered using Cntrl+Shift+Enter

and
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5<TODA Y()))
=SUMPRODUCT((ISNUMBER(FIND(A9,A1:C5)))*(D1:D5TODA Y()))

assuming column D are dates and not text.

" wrote:

Hello

I have a problem with vlookup function. I have three columns with
numbers a one column with a date in my excel table. Smth like this:


A B C D

11 12 13 09.12.06.
21 22 23 09.13.06.
31 32 33 09.15.06.
41 42 43 09.18.06.
51 52 53 09.17.06.

I need a formula that will check columns A to C for a certain number
(example: 32) and return the date from the matching cell from column D
(09.15.06).

--------------------

After i've done that .. i need the following ...

I need to count the number of cells which have the first number for
example "1" (11,12,13) and then check for each if date in column D
(09.12.06.) is greater or less then current date. Result should be
number of cells starting with number 1 which have date in column D
greater then current date, and number of cells which have date in
column D lesser then current date.

Thank you