Thank you for your time!
Come visualizzare le due date e restituire il valore corrispondente in Excel?
Supponendo che tu abbia una relazione su alcuni programmi, ora vuoi ottenere il progetto specifico di un dato tempo che è tra due date date del programma. Prendiamo ad esempio i seguenti dati di screenshot, vorrei cercare la data indicata 8 / 13 / 2015, se questa data cade tra le date di inizio e di fine all'interno del rapporto, quindi restituire il nome del progetto corrispondente. In questo articolo, ti presenterò la funzione LOOKUP per affrontare questo problema.
Vlookup per un valore in una lista:
Con questa formula di Kutools for Excel, è possibile visualizzare rapidamente il valore esatto da un elenco senza formule.
Kutools for Excel: con più di 200 pratici componenti aggiuntivi di Excel, liberi di provare senza limitazioni nei giorni 60. Scarica e prova gratis ora!
Per ottenere il valore relativo in cui la data cade tra due date, la seguente formula di ricerca potrebbe aiutarti. Per favore, fai come questo:
Inserisci questa formula in una cella vuota in cui desideri inserire il risultato: =LOOKUP(2,1/($A$2:$A$7<=F1)/($B$2:$B$7>=F1),$C$2:$C$7), (A2: A7 è l'intervallo di date di inizio e B2: B7 è l'intervallo di date di fine nel tuo rapporto, il F1 è la data specificata a cui si desidera ottenere il valore corrispondente.), quindi premere entrare chiave per ottenere il risultato che ti serve. Vedi screenshot:
Note: Se la data non esiste, si otterrà un valore di errore dopo aver digitato questa formula.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoSame here, still finding the way to retrieve more than one value, Did you find the solution?
Thank you for your time!
To post as a guest, your comment is unpublished.· 2 months agodear, I need help. For Example, IF we have many entries against value "A" than how I can pick the latest date value using the formula in excel. and if I have many entries of different values how I can pick the latest date value. (Sorry For My English)
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 3 months agoAdditional explanation: https://exceljet.net/formula/lookup-value-between-two-numbers
To post as a guest, your comment is unpublished.· 4 months agoHi. What is the logical explanation to use this formula: =LOOKUP(2,1/($A$2:$A$7<=F1)/($B$2:$B$7>=F1),$C$2:$C$7)? How does it work? Thanks.
To post as a guest, your comment is unpublished.· 4 months agoa godsend - exactly what I was looking for! much thanks
To post as a guest, your comment is unpublished.· 6 months agohello, what if in the columns, there are data aside from dates. How can I get the date from the range?
E.g. Column A - 03/19/2019, Column B - Approved, Column C - Pending, Column D - ---. In this case, how can I get 03/19/2019?
To post as a guest, your comment is unpublished.· 1 years agoi have two sheets, Sheet No 1 has Selling date, Grade & Item Code, Sheet no 2 has Price applicable from date, to date, Item code & Garde. please help to solve using vlookup, index or any other function in excel ?
To post as a guest, your comment is unpublished.· 1 years agoHello, what if I want to add another criteria. For example, if there was another column D called "Group ID" that I want to add in the formula. i.e. If group ID=302511 then LOOKUP(2,1/($A$2:$A$7<=F1)/($B$2:$B$7>=F1),$C$2:$C$7). Is this possible?
To post as a guest, your comment is unpublished.· 1 years agoHi
I have a list of Jobs where the status of these jobs vary from INV, CMP to CLS.
is there a way i could lookup the Status Date to a corresponding job number to a different sheet
To post as a guest, your comment is unpublished.· 1 years agoThanks - can always rely on help from internet
To post as a guest, your comment is unpublished.· 1 years agoI have two tables in a workbook.I need help to create a macro to run a report based on the dates as shown.
Name Due Date Amount Paid
JohnDoe3 23-Jan-18 $965.00 Yes
JohnDoe4 24-Jan-18 $145.00 Yes
JohnDoe5 25-Jan-18 $225.00 No
JohnDoe6 26-Jan-18 $336.00 No
JohnDoe7 27-Jan-18 $365.00 No
JohnDoe8 28-Jan-18 $124.00 No
JohnDoe9 29-Jan-18 $321.00 No
JohnDoe10 30-Jan-18 $333.00 No
JohnDoe11 31-Jan-18 $521.00 No
JohnDoe12 01-Feb-18 $333.00 Yes
JohnDoe13 02-Feb-18 $965.00 No
JohnDoe14 03-Feb-18 $145.00 No
JohnDoe15 04-Feb-18 $225.00 No
JohnDoe16 05-Feb-18 $336.00 No
JohnDoe17 06-Feb-18 $365.00 No
JohnDoe18 07-Feb-18 $124.00 No
JohnDoe19 08-Feb-18 $321.00 No
JohnDoe20 09-Feb-18 $333.00 No
JohnDoe1 10-Feb-18 $521.00 No
JohnDoe2 11-Feb-18 $333.00 No
FirstDate LastDate PayDay
19-Oct-17 01-Nov-17 16-Nov-17
02-Nov-17 15-Nov-17 30-Nov-17
16-Nov-17 29-Nov-17 14-Dec-17
30-Nov-17 13-Dec-17 28-Dec-17
14-Dec-17 27-Dec-17 11-Jan-18
28-Dec-17 10-Jan-18 25-Jan-18
11-Jan-18 24-Jan-18 08-Feb-18
25-Jan-18 07-Feb-18 22-Feb-18
08-Feb-18 21-Feb-18 08-Mar-18
Today is 05FEB18
If I run a report today (falls between 25-Jan-18 and 07-Feb-18 for pay period 22-Feb-18)
I want to pick up all the records for Due Dates 25JAn18 to 07FEB18 which has no in the Paid column and create a report.
Then update the paid column to yes.
the output should be as follows
JohnDoe5 25-Jan-18 $225.00
JohnDoe6 26-Jan-18 $336.00
JohnDoe7 27-Jan-18 $365.00
JohnDoe8 28-Jan-18 $124.00
JohnDoe9 29-Jan-18 $321.00
JohnDoe10 30-Jan-18 $333.00
JohnDoe11 31-Jan-18 $521.00
JohnDoe12 01-Feb-18 $333.00
JohnDoe13 02-Feb-18 $965.00
JohnDoe14 03-Feb-18 $145.00
JohnDoe15 04-Feb-18 $225.00
JohnDoe16 05-Feb-18 $336.00
JohnDoe17 06-Feb-18 $365.00
JohnDoe18 07-Feb-18 $124.00
Thanks for your help.
To post as a guest, your comment is unpublished.· 2 years agoHi,
pls. explain how the formula works. Why use "2" as lookup values and "1/" in lookup vector?
To post as a guest, your comment is unpublished.· 2 years agoHow to solve the following problem
Date Range 50000 100000 200000
01/07/2010 30/06/2011 460 920 1840
01/07/2011 29/02/2012 502 1005 2011
01/03/2012 22/05/2015 560 1120 2241
23/05/2015 16/05/2017 480 960 1920
I want investment return of $100000 for the date 03/04/2013. What will be the formula?
To post as a guest, your comment is unpublished.· 2 years agoCan you explain why this works? What is the 2,1 for?
To post as a guest, your comment is unpublished.· 2 years agosir how this formula work,pls get me step by step
To post as a guest, your comment is unpublished.· 2 years agoYes, Why 2,1/
In fact I need to Vlook up a date in a range of dates like bellow, but th formula doesn't work #REF ! I don't know why..
2010-04-01 2010-10-31 À date P-6
2011-04-01 2011-10-31 À date P-5
2012-04-01 2012-10-31 À date P-4
2013-04-01 2013-10-31 À date P-3
To post as a guest, your comment is unpublished.· 2 years agoWhen try with this in F2 =Lookup(F1,$A$2:$B$7,$C$2:$C$7) got the similar result. Can you pl explain the difference and why 2 been used as lookup value.
To post as a guest, your comment is unpublished.· 1 years agoI think it's because that's the number of lookup factors you have. I tried it with three factors and I used 3 as the lookup value. I'm not sure exactly, but I think by putting "3" and then using the "/" to separate the lookup vectors you tell Excel to search for the three following inputs to return the output.
To post as a guest, your comment is unpublished.· 3 years agoThis is brilliant. Simplicity in explanation is perfect. One of the quickest formulae I've ever looked up to learn. Thank you.
To post as a guest, your comment is unpublished.· 3 years agoHi! Nice explanation, but how can we do the same exercise if we have multiple projects with the same dates? In that case I want the name of those projects to be returned not only a single value...