Conver values into text

I have sheet 1 with a column "P" where the cells in that column may contain numbers "1, 2, 3a, 3b, 3c, 4, 5, 6, 7, 8, 9, 10" the cells will vary so P2 may contain "2, 3a, 10" and P3 may contain "2, 3a, 3b, 5, 6" and so on, also P4 may contain no values.

I need a formula so if cell "P2" contains the values "2, 3a, 10" then the formula will look at a different Sheet 3 (see below) and pick out "Management Fee, Loan Interest and Tenancy Registration Fees" and combine this text in cell "AX2", The text if possible should have the code then a space the text and then a carriage return.

Sheet 3 information below



So what I am trying to achieve is that the cell "AX2" would be as follows:

2 Management Fee
3a Loan Interest
10 Tenancy Registration Fees

I will be copying this formula down about 1500 cells.

I have tried split the values in one cell by "Text to Columns" and then I have used "=CONCATENATE(VLOOKUP(BB2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BC2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BD2,Sheet3!$A$2:$B$14 ,2,FALSE),VLOOKUP(BE2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BF2,Sheet3!$A$2:$B$14,2,FALSE), VLOOKUP(BG2,Sheet3!$A$2:$B$14,2,FALSE),VLOOKUP(BH2,Sheet3!$A$2:$B$14,2,FALSE), VLOOKUP(BI2,Sheet3!$A$2:$B$14,2,FALSE))"

if works great for a where all the values are used, but if I have two values in one row and the next row down I will have values, since some cells will be blank, this is giving N/A in the cell AX2, I am told I could try adding =concatenate(if(isna(vlookup..... but this is giving to many arguments and I cannot find the problem.


Thanks,
John

Hello,

I have just figured out how to do the above problem with alot of help from someone, but now I am wondering if it is possible to write a formula that will automatically convert cell values i.e. "1, 2, 3a, 3b, 3c, 4, 5, 6, 7, 8, 9, 10" to "Text to columns" command if you state the cell you want to begin at.

Thanks,
John

If you are getting help on another forum, please place the link to that thread here, so that we know the solution status.