Look or VLookup ??

[Nitin Shardul]

Dear Sir,

Please find attached excel filed want arrange employee dependent by looking up emp code

Thanks,
N3

 

[Khalid NGO]

Hi,

Your raw data is inconsistent, I think formula solution will be very complicated; but, if pivots are allowed, your report will looks something like this, (with some helper columns)



See if it helps.

Regards,

 

[Nitin Shardul]

Hi,

Your raw data is inconsistent, I think formula solution will be very difficult; but, if pivots are allowed, your report will looks something like this, (with some helper columns)

View attachment 27284

See if it helps.

Regards,

Thanks
it solves the query, How those employee code (a) & employee code (b) derived ?

 

[Khalid NGO]

Thanks
it solves the query

Glad it solved

How those employee code (a) & employee code (b) derived ?

I have created helper columns K to N in raw data sheet, so we can easily filter with pivot.
Formulas are very simple, you will understand by going through each.

Regards,

 

[Gcusty]

Help? I'm not sure how to post a question, it's not clear to me, so hope you can help me. I need to make a formula to look up a list of program titles in a long list. The names won't match perfectly, so I am looking to find a way to lookup a partial match and confirm that the title is in the list. For example, I will look up a name like scandal in a list of over 1000mprogram names which are better describe as scandal - season 1. So how can one use a formulas to lookup this type of list.

Thank you, for any help. For your background. I am fairly new to excel. And use it for general work as a data entry clerk. This is a struggle I have for a current project which will help me on my temp job I currently hold.

 

[Khalid NGO]

Hi @Gcusty and welcome to the forum

Being a new user I suggest you to start from here:
http://chandoo.org/forum/forums/new-users-please-start-here.14/
http://chandoo.org/forum/threads/new-users-please-read.294/


You can post your question by clicking "Post New Thread" at the following link:
http://chandoo.org/forum/forums/ask-an-excel-question/

Posting a sample excel file with expected output will help you to get quicker and targeted solutions.

Regards,

 

[bosco_yip]

Try this formula solution way.

1] Data require format Sheet, E6 enter array formula (Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER)

=INDIRECT("'raw data'!"&TEXT(SMALL(IF((ISTEXT('raw data'!$B$4:$F$50))*('raw data'!$B$4:$F$50<>"Male"),ROW($4:$50)/1%+{2,3,4,5,6},9099),ROW(A1)),"R0C00"),)&""

2] Data require format Sheet, D6 enter formula

=IFERROR(INDEX('raw data'!A$4:A$50,MATCH(E6,'raw data'!B$4:B$50,0)),"")

All copy down

Regards
Bosco

 

[Khalid NGO]

@bosco_yip awesome
Trying to digest your 1st formula

 

[Nitin Shardul]

Try this formula solution way.

1] Data require format Sheet, E6 enter array formula (Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER)

=INDIRECT("'raw data'!"&TEXT(SMALL(IF((ISTEXT('raw data'!$B$4:$F$50))*('raw data'!$B$4:$F$50<>"Male"),ROW($4:$50)/1%+{2,3,4,5,6},9099),ROW(A1)),"R0C00"),)&""

2] Data require format Sheet, D6 enter formula

=IFERROR(INDEX('raw data'!A$4:A$50,MATCH(E6,'raw data'!B$4:B$50,0)),"")

All copy down

Regards
Bosco

Thanks Bosco,

But unable to change the same while entering more data
Please find attached data

Thanks
Nitin

 

[Deepak]

Nice! bosco_yip

 

[Nitin Shardul]

Dear Bosco
formula matched for next 50 rows but didn't workout further
Please check
Thanks
Nitin

Thanks Bosco,

But unable to change the same while entering more data
Please find attached data

Thanks
Nitin