Formula for if L21 equals column AC then return value in AD to K21

[CLCoop]

Fun times here. I have a Branch name in Column L21(which is a formula taking a branch name selected from Column C14 (drop down to select other branches) but always start in L21. I need Column L21 to look at Column AC2:AC40 and if a match then take what is in column AD (depending on where it finds the match in AC2:AC40 and put it in column K21

I've tried this and a few other codes but keep getting #N/A

=VLOOKUP(L21,$AC$2:$AC$40,1,FALSE)

Can someone one point me a way that I won't go crazy

Thank you for your help in advance

 

[CLCoop]

I have also tried this VBA code to get this to work...


Dim Dept_Row as Long
Dim Dept_Clm as Long
Table1=Sheet1.Range("L21:L70") 'Branch names that will change as different Branches are selected I have it copying C14 with a formula =C14 to autopopulate
Table2=Sheet1.Range("AC1:AE40") 'Branch name to match L21:L70
Dept_Row=Sheet1.Range("K21").Column
For Each cl in table1
Sheet1.Cells(Dept_Row,Dept_Clm)=Application.worksheetfunction.vlookup(K21,Table2,1,False)
DeptRow =Dept +Row +1
Next cl
MsgBox Done​

 

[JoeMo]

In K21:

=IF(ISNA(MATCH(L21,$AC$2:$AC$40,0)),"",INDEX(AD:AD,MATCH(L21,$AC$2:$AC$40,0)))

 

[CLCoop]

Yea got it to work with your help. Sorry I didn't respond sooner I ended up taking Friday off due to not feeling well. Here is the formula I got to work, yes I added a column so everything moved over one to the right and I used the hidden B15 column rather than the one you could see as for some reason it was always coming back with an answer that was just one level above what was needed.

=IF(ISNA(MATCH(B15,$AD$2:$AD$40,0)),"",INDEX(AE2:AE40,MATCH(B15,$AD$2:$AD$40,0)))

B15= Pivot table sorted by Branch
AD2:AD40: what I was matching to B15
AE2:AE40: if AD and B matched then put what is in AE in the field of which this formula is located


Again thank you for your time and help.

 

[JoeMo]

You are welcome - thanks for the reply.