• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to bring back (vlookup) text values (multiple) based on date range?

P

PARTYzanka

New Member
Hi everyone!


I need help to create a formula that brings back text values based on date range.

For ex,. In the dynamic table I have a list of IDs (column A), value in column B is pulled from the other spreadsheet, which is a list of comments (notes).


Dynamic tab:

date start - (can be 1/1/2012-12/1/2012)

date end - (can be 1/1/2012-12/1/2012)


column A - ID

column B - text value (from the spreadsheet, column C)


Spreadsheet:

column A - ID

column B - date (can be 1/1/2012-12/1/2012)

column C - note (text value)

One ID can have multiple notes posted on different dates.


So, if I choose in the Dyanmic tab the date range from 1/1/2012 to 5/1/2012, I want to see in the column B all the notes (text values) from the spreadsheet for the same date range for the particular ID.


I've tried SUMIFS function. Unfortunately, it does not bring back the text values.


I hope I explained it well. Any help will be appreciated!!!
 
Put this in column a and drag down. You have to change the fillers to the correct cells

=if(and(b1>=startdate,b1<=enddate),INDEX(a:a,MATCH(b1,b:b)),"")


Put this in column b and drag down.

=if(and(b1>=startdate,b1<=enddate),INDEX(c:c,MATCH(b1,b:b)),"")


Upload a file if you need more help.
 
Hi Montrey! thank you so much for your reply. I've uploaded the file here : https://www.dropbox.com/s/b87xd0he64d6gs7/Excel1.xlsx The original file is more complicated, so I've made a small version of it. Thanks again!
 
Hi ,


In B7 use the following formula entered as an array formula ( using CTRL SHIFT ENTER ) :


=IFERROR(INDEX($C$16:$C$21,MATCH(1,(($B$16:$B$21=$A7)*($A$16:$A$21>=$B$3)*($A$16:$A$21<=$B$4)),0)),"")


In C7 , modify the above formula to index the column D , and in D7 , modify the above formula to index the column E. Copy the resulting formulae down.


Making it more explicit , in C7 , enter the following array formula :


=IFERROR(INDEX($D$16:$D$21,MATCH(1,(($B$16:$B$21=$A7)*($A$16:$A$21>=$B$3)*($A$16:$A$21<=$B$4)),0)),"")


In D7 , enter the following array formula :


=IFERROR(INDEX($E$16:$E$21,MATCH(1,(($B$16:$B$21=$A7)*($A$16:$A$21>=$B$3)*($A$16:$A$21<=$B$4)),0)),"")


Copy these three formulae down.


Narayan
 
Hi NARAYANK991! Thanks a lot for your help!


I've tried these fromulas. They worked great to bring back the first notes of the selected date period. Do you know how to change the formula so it would bring back all the notes related for the selected period?

For ex., if you select the date period from 1/1/2012 to 5/1/2012 for ID #1, it would bring back in one cell (B7) all the notes (two) for this period?


thanks!!!!!
 
Hi, PARTYzanka!


Just read while passing by... but I'm afraid that you can't do that in the one cell without an UDF (user defined function, VBA code, aka Macro).


Regards!


PS: Maybe a little late, but I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.

Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
 
I'm pretty sure you cant bring multiple cell values back in one cell. maybe an excel ninja knows a way ;)
 
@Montrey

Hi!

Not this one for sure... maybe David Copperfield brother Harry Potter cousin but not me.

Regards!
 
Guys, please, I still believe in Santa!


If it's not possible to do it in one cell, then may be in a few next to each other?
 
I figured it out.

Put this in b7, and drag down or accross as needed :D


=IF(AND($A16>=$B$3,$A16<=$B$4,$A7=$B16),C16,"")
 
If you put that formula in b7 of the file you uploaded. It works. It looks at the date range, and pulls the comments for the user id's between the date range.


Please explain what else you are trying to do! so we can further help!

I want to figure this out ;)
 
Montrey, I want to figure this out too, but it looks like that the "mission is impossible".


This is what I'm trying to do:

1)If I choose the start date from 1/1/2012 to end date 5/1/2012 for ID#1, Team1 I should see in the column B7: error;correct

2)If I choose the start date from 2/1/2012 to end date 6/1/2012 for ID#1, Team1 I should see in the column B7: error;fixed


This is what I get from your solution :

1)error

2)


Thank you, thank you, thank you.
 
I tried but I could not figure it out. the data will have to be set up differently I believe. I used vlookup&vlookup but thtey bring back the first instance of the user id. I'm not sure how to have the second vlookup find the next instance of the user id after the user id has been found.


:(
 
Hi ,


Try using the following formula in E7 , and copy it across to F7 , G7 and so on ; remember to enter it as an array formula ( using CTRL SHIFT ENTER ) :

[pre]
Code: 
=IFERROR(INDEX($C$16:$C$21,SMALL(IF((($A7=IDs_Range)*(Dates_Range>=Start_date)*(Dates_Range<=End_date))>0,ROW(IDs_Range)-15,99),COLUMN(E7)-4)),"")
[/pre]
I have used range names for convenience ; they are :


IDs_Range : =Sheet1!$B$16:$B$21


Dates_Range : =Sheet1!$A$16:$A$21


Start_date : =Sheet1!$B$3


End_date : =Sheet1!$B$4


The number 4 in the formula ( COLUMN(E7)-4 ) is because column E is the fifth column , and the result of the above calculation will result in 1 when the formula is in column E , 2 when it is in column F , 3 when it is in column G and so on.


The number 15 in the formula ( ROW(IDs_Range)-15 ) is because IDs_Range is from row 16 through row 21 ; ROW(IDs_Range) will return an array of numbers { 16,17,18,19,20,21 } and subtracting 15 from this array will result in another array { 1,2,3,4,5,6 }.


Narayan
 
Wow, it worked! Bravo, Narayan!!!


So, it looks like that I need to modify my tab to have the extra columns in order to bring all the notes.. Which is not a big problem.. It’s doable.

The problem that I see is that using array formulas in my original file slows down calculations. I’m wondering if there is a way to turn off these formulas when I don’t need them? I’m going to use my file on a daily basis, but the tab with the formulas above only once a month..


Anyway, I’m going to study this formula to understand how it works. I’m very grateful to everyone who tried to help and especially to Narayan who solved the problem!

Great forum! I’m happy that I found it. You, guys, are the best!!!!
 
Hi ,


Thanks for the appreciation. Happy to help. Keep visiting.


Probably , as SirJB7 mentioned , if you use VBA , calculation time would be less.


Narayan
 
Narayank you truly are a ninja! my head was starting to hurt yesterday trying to come up with some crazy formula! but looks like u did! Awesome! Now I want to know how it works!
 
Now that I look at it, This is what I was trying to do! I was just having trouble finding the row and column number!!


Ima examine how u got the Row & Col. Please explain if u have time!
 
@Montrey

Hi!

You're improving...

No more having trouble finding networks, computers, drives, folders, workbooks and worksheets?

Fine! It only rests rows and columns :)

Regards!

PD: Should I add it's a joke? If I should, I won't; if I shouldn't, neither

:)
 
You must log in or register to reply here.
Back
Top