Hi all,
I have a question about lookup value here.
If I have a table like below, and I wish to know example like what Jamal can do, I want to search Jamal's row which tick with a "X" then display the job he can do. The answer should display Procurement and Finance.
For Arif, the answer should show Sales, Finance and Warranty.
For Jane, it should display Sales, Procurement, Finance and Order.
Please find the attachment at below.
Can anyone help in this issue?
Thanks.
Last edited by xiongling; 11-26-2011 at 06:36 PM.
The formula might be a little bit long, but it does the trick. Forgot to add trim function to the formula in the file.
=Trim(IFERROR(INDEX($A$1:$F$1,IF(ISBLANK(B2),"",COLUMN(B2))),"")&" "&IFERROR(INDEX($A$1:$F$1,IF(ISBLANK(C2),"",COLUMN(C2))),"")&" "&IFERROR(INDEX($A$1:$F$1,IF(ISBLANK(D2),"",COLUMN(D2))),"")&" "&IFERROR(INDEX($A$1:$F$1,IF(ISBLANK(E2),"",COLUMN(E2))),"")&" "&IFERROR(INDEX($A$1:$F$1,IF(ISBLANK(F2),"",COLUMN(F2))),""))
Last edited by JieJenn; 11-26-2011 at 06:57 PM.
Here is an example with vlookup.. this will allow you to build as you go and select a specific employee.
Hello xiongling,
See the attached. If you are using XL2010, you can use AGGREGATE function to avoid Ctrl+Shift+Enter.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
Hi all,
Thanks for reply...
JieJenn, I got your formula works but is a bit too long if I'm having 100 differents job to assign to the user...
Haseeb A, I wish your formula can work but I've tried few times also can't get it...Could you please give me a clearer explanation on it? Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks