I could really use someone's help with this. The problem with vlookup is that it returns the corresponding value in the first instance the formula finds the lookup value in the referenced column. I can't figure out a formula that would consider three (four) columns, A(Name), B(Key), C(Type). A is the ultimate lookup value, then if C contains a specific value, then return value in B. Column B is the most unique (never duplicate values).
Basically, this is the gist of what I want:
Look up "Andrew" in A, then see if "Andrew" in A has value "Economics" in C, if so, then return value in B.
To take this a bit further: Return all corresponding values in B for every instance in which "Andrew" in A has a corresponding value "Economics" in C. Concatenate these B values.
If it can't get any more complicated, I'd would like to concatenated the corresponding dates (Column D-Key Date). I figured out how to do this perfectly, but I am not sure how the dynamics/formula will reflect based on the above formulas for what I want to achieve.
Corresponding Dates of each Key to be concatenated and placed within parenthesis.
Below is the formula I've used until I realized the flaw as mentioned at the top.
=IF(VLOOKUP($F14,$A$3:$C$10,3,FALSE)="Architecture",VLOOKUP($F14,$A$3:$B$10,2,FALSE),"")&" "&IF(NOT(VLOOKUP($F14,$A$3:$C$10,3,FALSE)="Architecture"),"",IF(ISBLANK(VLOOKUP($F14,$A$3:$D$10,4,FALSE)),"(Not Submitted)","("&TEXT(VLOOKUP($F14,$A$3:$D$10,4,FALSE),"m/dd/yy")&")"))
I should say that that parts in red doesn't work, hence the post.
If you can figure out a way to solve this, then I'd be most grateful. If you can also explain what I am doing wrong, then please let me know because I am still learning Excel.
Thanks in advance,
Andrew
Bookmarks