• 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.

Lookup and paste names based on certain condition

P

poren123

New Member
Hi, I have set a name list with all the employees, but the report (in another sheet) should only show those who pass probation. I have added another column to mark those count, but I have problem looking up those names. The only formula seems to work is INDEX+MATCH, but it only show the first match. Could someone help? Thanks
 
Hi Poren,


I am not sure if I got your query correctly..


Are you trying to fetch multiple parameters for single lookup value? If yes, let's assume the following:


In sheet1 you have data as follows(in Col A and Col B):


Name >> Data

A>>>>>> 4

B>>>>>> 5

A>>>>>> 2

A>>>>>> 8


In sheet2 you have the name say, "A" at A2. Now for "A", you want to lookup the data column from sheet1.


At b2 of sheet2, write the following:

=IFERROR(INDEX(Sheet1!$B$2:$B$5,SMALL(IF(Sheet1!$A$2:$A$5=Sheet2!$A$2,ROW(Sheet1!$A$2:$A$5)-1,""),ROW(Sheet1!A1))),"") (press CTRL+SHIFT+ENTER) to enter it as an array formula and copy it down as needed. This will give you all the values(4,2,8) for "A" (not only the first match).


Change the index array reference and all other reference in the formula per your need.


If this is not something that you are looking for, then please upload a sample file here for our better understanding of your requirement:


http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Kaushik
 
Hi Kaushik,


Thanks for the reply. I'm sorry that I didn't make myself clear. What I means is that I want to lookup the employees (as shown below as Employee#) who past probation (1 means probation completed) in a new sheet.


Employee# Probation

1>>>>>>>1

2>>>>>>>0

3>>>>>>>1

4>>>>>>>1

5>>>>>>>0

6>>>>>>>1

7>>>>>>>1

8>>>>>>>1


I wrote a formula as: =INDEX('Name List'!$A:$A,MATCH(1,'Name List'!$B:$B,0),COLUMN(A$1))

But it only returns the employee No.1.


Thanks,

Poren
 
Hi Poren,


Thank you for providing the clarification.


With same approach with little change in the formula reference will do the job.


I assume you have the above data at Sheet1(from A1 to B9).In sheet2, at a2 you have the probation marker i.e. 1. Now at b2 write the following formula:


=IFERROR(INDEX(Sheet1!$A$2:$A$9,SMALL(IF(Sheet1!$B$2:$B$9=Sheet2!$A$2,ROW(Sheet1!$B$2:$B$9)-1,""),ROW(Sheet1!A7))),"")

(press CTRL+SHIFT+ENTER) to enter it as an array formula and copy it down as needed. This will give you all the values(employees' #) 1,3,4,6,7 and 8 for the probation marker 1.


Hope this helps.


Please let us know if this is fine....


Kaushik
 
Hi Kaushik,


Thanks again. I tried the formula you provided, but the SMALL() part turns out to be #NUM! (the IF() part works just fine), so all the values are blank. I did exactly as you said, but cannot figure out why. Furthermore, I did not understand why use ROW(Sheet1!A7) in the SMALL().


Sorry I can't upload my spreadsheet since the company blocks all the file sharing websites.


Poren
 
Hi Poren,


It's my mistake...I posted you the wrong formula...I am sorry. Here is the correct one:

=IFERROR(INDEX(Sheet1!$A$2:$A$9,SMALL(IF(Sheet1!$B$2:$B$9=Sheet2!$A$2,ROW(Sheet1!$B$2:$B$9)-1,""),ROW(Sheet1!A1))),"")


ROW(Sheet1!A1) is giving you the K' th smallest number from the array


Please see the file from here in order to examine the formula better.


http://speedy.sh/2f8Qa/Poren-for-Chandoo-forum.xlsx


Please let me know if you want me to explain the formula in detail.


Kaushik
 
It works! Thanks so much, Kaushik!


I knew how it works, but could this be automatically updated when I put new data in the sheet1?
 
Hi Poren,


Glad that it works.


As of now we have used absolute reference at sheet1. To make it dynamic, I would suggest you to create dynamic named range for col A and B of sheet1 as follows:


Go to Name manager(keyboard shortcut ctrl+f3)...in name box give any name(e.g.ColAdyn) and ion refer to box write =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A,0)-2,1)

This will make column A dynamic


In order to make column B dynamic, do the same as follows:


In name box, give a name (for e.g. ColBDyn)

In refer to box write: =OFFSET(ColAdyn,0,1)


Now pass these pass these references in the formula. Hence, our formula becomes:


=IFERROR(INDEX(ColAdyn,SMALL(IF(ColBDyn=Sheet2!$A$2,ROW(ColBDyn)-1,""),ROW(Sheet1!A1))),"") (press ctrl+shift+enter)...and copy it down as you need(say till 100 rows)


Now at sheet1, if you write 9(at A10) and 1 at (B10), B8 of sheet2 will be populated with 9 automatically.


Let me know if it's fine...


Kaushik
 
hi, i have a sheet which contains a receipt. now i want sheet 2 to contain all details of every receipt i issue using either formalae or a macro.
 
Hi Kaushik,


I got another similar problem:


I tried to use data validation to make a dependent dropdown list of all the employees (the same name list sheet above) based on their titles, so as to show the data accordingly in dashboard.


I have created a secondary sheet which contains all the titles as follow:


1st>>>>>>>>>>>>>2nd

SM>>>>>>>>>>>>>SC

DM>>>>>>>>>>>>>AM

>>>>>>>>>>>>>>>SA

>>>>>>>>>>>>>>>SB


I've named the 1st level as Lev1st, 2nd as Lev2nd, and I tried to create another table including the employees' names based on the level for the use of data validation:


1st>>>>>>>>>>>>>2nd

A>>>>>>>>>>>>>E

B>>>>>>>>>>>>>F

C>>>>>>>>>>>>>G

D>>>>>>>>>>>>>H

>>>>>>>>>>>>>>I

>>>>>>>>>>>>>>J

>>>>>>>>>>>>>>K


I used the formula you provided above: =IFERROR(INDEX(NamFulNam,SMALL(IF(IFERROR(NamTit=Lev1nd,""),ROW(NamFulNam)-1,""),ROW('Name List'!B2))),"")

but it always returns 0.


Could you help me on this formula? I can't find out where went wrong.


Thanks,

Poren
 
Hi Poren,


Can you plz go through the link and adapt trhe technique to your need?

http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/


Let me know in case you want me to do this. Very busy now.....will post you tomorrow...


Thank you for your understanding and co-operation.


Regards,

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