The listing is 1 to 1 vs level.
How to search the value that match minimum criteria.
Please refer to file attached.
Thanks
The listing is 1 to 1 vs level.
How to search the value that match minimum criteria.
Please refer to file attached.
Thanks
It's not exactly clear to me what your rules are.
Is column C on the report sheet the results you're looking for? If so please explain how you determine the values.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
Yes, column C is the result that i looking for.
Imagine it into an organization chart. It should keep up-search until the approval manager with minimum level of Manager then grab the value.
The tab [Database] is 1 to 1 reporting listing.
Work sheet function will be lengthy. If code increases further from 7 formula has to be modified. So I have given UDF SearchManager And code for that below. Irrespective of maximum number in code UDF works without any modification.
Default Minimum Value is taken as 4 (Manager)as per your requirement. If it is to be changed change the Number 4 in the below line in code.
Do While X > 4
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
What's the logic for these results? Makes no sense to me at all.
Excel 2016 (Windows) 32 bit
F G H 6 Gary Search Eddy 7 Kolk Search Eddy 8 Ivy Search Eddy 9 Maggi Search Eddy 10 Oscar Search Eddy
Sheet: Database
However, this will produce the desired results:
=IF(VLOOKUP(A4,Database!A:D,4,0)<4,VLOOKUP(A4,Database!A:B,2,0),INDEX(Database!B:B,MATCH(MIN(4,VLOOKUP(A4,Database!A:D,4,0)),Database!D:D,0)))
Excel 2016 (Windows) 32 bit
A B 3 Employee To Be Approval Manager 4 Amy Ben 5 Cathy Amy 6 Eddy Cathy 7 Gary Eddy 8 Kolk Eddy 9 Ivy Eddy 10 Maggi Eddy 11 Oscar Eddy 12 Queeny Cathy 13 Selly Amy 14 Mickel Nichloas 15 Fish Eddy
Sheet: Report
Last edited by AliGW; 04-19-2019 at 02:47 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
How come Nichloas. Eddy is nearest code. AliGM formula work perfect.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
The maximum code is less or equal to 4
Fish report to Mickel and Mickel is report to Nichloas.
Michel level code is 5 and Nichloas code is 3.
So answer is Nichloas.
Thanks
OK - try this:
=IF(VLOOKUP(A4,Database!A:D,4,0)<4,VLOOKUP(A4,Database!A:B,2,0),IF(VLOOKUP(A4,Database!A:D,4,0)=5,VLOOKUP(VLOOKUP(A4,Database!$A$3:$B$14,2,0),Database!$A$3:$B$14,2,0),INDEX(Database!B:B,MATCH(MIN(4,VLOOKUP(A4,Database!A:D,4,0)),Database!D:D,0))))
I am pretty sure it will fail when you add more people to your list, though.
Unable to open the file. Pl upload file without putting any code.
There is nothing to change the code. Only proper ranges are to be selected as given in file. Ranges with names is to be selected.
Uploaded. Thanks
Btw range with the name? I couldn't find there the range or name store.
Pl see file.
In the uploaded file the code for UDF in not pasted to module. How to paste the code is given in my previous post. In the formula also ranges selected were not correct.
Here is the revised code for UDF.
Please Login or Register to view this content.
If I want to use Employee ID [A:A] in tab report as search based, how to change to formula? Thanks
Which formula? Do you mean the code?
Revised code for UDF is here. So formula is also changed.
CodePlease Login or Register to view this content.
Please Login or Register to view this content.
YEAH!!! I managed to found and got the answer. Thanks All!!
Can I just change the formula as below, without revised the code?
Formula:Please Login or Register to view this content.
Thanks
Last edited by Shermaine2010; 04-23-2019 at 08:30 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks