Hello,
i made a list in sheet 2 names "workers"
in sheet 1,
i want to find last cell in column b that contains one of the names in the list "workers" and also having a value in column c and d, and finally return the corresponding date in column a.
Here is an attached sample for better understanding. Thanks
With an pivot table.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
hi childzzz. try this non-array formula as an alternative:
Please Login or Register to view this content.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
=lookup(2,1/(isnumber(match(b2:b7,workers,0)))/(c2:c7<>"")/(d2:d7<>""),a2:a7)
Last edited by Teethless mama; 01-05-2013 at 11:55 AM.
Using your posted workbook...
This regular formula returns the Col_A date associated with the last Col_B value that matches a Workers list item AND has values in Col_C and Col_D
Is that something you can work with?Please Login or Register to view this content.
If I understand what you want to do...
This formula entered in B1 and copied down:
=IFERROR(LOOKUP(2,1/((Sheet2!B$2:B$7=A1)*(Sheet2!C$2:C$7<>"")*(Sheet2!D$2:D$7<>"")),Sheet2!A$2:A$7),"")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks oeldere, but I don't like working with pivot tables.
@ benishiryo, thanks man it works.
One more question, if I make a second list named "WORKERS2", and create a dropdown list in cell B14, how can I adjust the formula to automatically change based on my selection in cell B14? can I just replace WORKERS in the formula with B14? I will attach another sample. Thank you all for your quick replies
Try with INDIRECT (with thank benishiryo);
=LOOKUP(2,1/(ISNUMBER(MATCH(B2:B7,INDIRECT(B14),0)))/(C2:C7<>"")/(D2:D7<>""),A2:A7)
Quang PT
I noticed your reaction, but once you've seen the possibilities of pivot table, you "only" will work with pivot table.Please Login or Register to view this content.
So i try again with an solution with pivot table. (see the attached file).
THANKS ALL for your help. Much appreciated
Non-Volatile Solution:
=LOOKUP(2,1/(ISNUMBER(MATCH(B2:B7,CHOOSE(MATCH(B14,{"WORKERS","WORKERS2"},0),WORKERS,WORKERS2),0)))/(C2:C7<>"")/(D2:D7<>""),A2:A7)
this one a little bit shorter
=LOOKUP(2,1/(ISNUMBER(MATCH(B2:B7,IF(B14="WORKERS",WORKERS,WORKERS2),0)))/(C2:C7<>"")/(D2:D7<>""),A2:A7)
That's a good solution, thanks @ Teethless mama,
but what if the list grows to be 3 or more (WORKERS,WORKERS1,WORKERS2...)??
Have you checked with INDIRECT in #8? I think it works.Originally Posted by chiidzzz
YES IT WORKS
Thankssss alot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks