| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
