Formula referencing 2+columns, rows based on conditions, returns value from another column

[andrew06]

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

[BB1972]

Ooooh, that was tough! I hope you're ok with helper columns (which can of course be hidden). The solution uses a UDF which I cannot take credit for (but also cannot remember where I got it), and I found it necessary to split your data to use the UDF (lookup_concat function). I've no doubt some of the real experts here can come up with a more elegant solution, but in the absence of one, this works

Let me know if you need any of what I have added (green background) explained.

Hope this helps.

[andrew06]

Brendan, this is brilliant!

I greatly appreciate your taking the time to come up with a solution. Although I haven't had the time to test these formulas on the real stuff from work, the solution you've suggested makes a lot of sense (i.e., function and interaction between the formulas). It's amazing how much time and energy people put into these things when, in the end, the solution is really a simple one.

I do want to ask about three things:
1) Helper Columns formulas: the "C3<>$F$2" part of the main IF formula really means C3 is not $F$2, right?
2) I never employed Helper Columns before. In regards to the project I'm working on, I have a main sheet in which I populate the cells with return values from a source that is saved in a different sheet. Do you recommend that I create the Helper Columns in the same sheet in which the source is located, or create them in the main sheet? I work with a lot of data.
3) I realized that I will need to add another variable to the equation, but I figured it out (in blue). =IF(C3<>$F$2,"",IF(D3="",B3&" "&"(Not submitted)",IF(E3="Yes",B3&" "&"("&TEXT(LEFT(D3,5),"m-dd-yy"&")"),B3&" "&"("&TEXT(LEFT(D3,5),"m-dd-yy")&" -Not Signed"&")")))

Thanks,
Andrew

[BB1972]

Andrew, you're welcome - glad I could help.

I do want to ask about three things:
1) Helper Columns formulas: the "C3<>$F$2" part of the main IF formula really means C3 is not $F$2, right?

Yeah, pretty much. I'm not being pedantic, but meaning is often lost on the internet, so: IF(C3<>$F$2 means "if the contents of C3 are not equal to the contents of F2".

2) I never employed Helper Columns before. In regards to the project I'm working on, I have a main sheet in which I populate the cells with return values from a source that is saved in a different sheet. Do you recommend that I create the Helper Columns in the same sheet in which the source is located, or create them in the main sheet? I work with a lot of data.

If it were me, I would have the helper columns on the source sheet.

3) I realized that I will need to add another variable to the equation, but I figured it out (in blue). =IF(C3<>$F$2,"",IF(D3="",B3&" "&"(Not submitted)",IF(E3="Yes",B3&" "&"("&TEXT(LEFT(D3,5),"m-dd-yy"&")"),B3&" "&"("&TEXT(LEFT(D3,5),"m-dd-yy")&" -Not Signed"&")")))

Not sure where the question is there - did you maybe figure it out (in blue) after originally posting?