Combining multiple Vlookups in one cell with text added

[brooksc29]

I am trying to make a cell pull in data from 5 other cells using Vlookup.

The final result needs to look like this in cell Q7.

1.1IP, 3H, 0ER, 0BB, 2K

The IP, H, ER, BB, and K need to be added after the data in front of it.

for IP this is the formula:
=VLOOKUP($A$2, LAST7DAYSP!B3:W17, 13, FALSE) & "IP,"

for H it's this:
=VLOOKUP($A$2, LAST7DAYSP!B3:W17, 14, FALSE) & "H,"

for ER:
=VLOOKUP($A$2, LAST7DAYSP!B3:W17, 16, FALSE) & "ER,"

for BB
=VLOOKUP($A$2, LAST7DAYSP!B3:W17, 17, FALSE) & "BB,"

for K:
=VLOOKUP($A$2, LAST7DAYSP!B3:W17, 19, FALSE) & "K"

is there a way to combine all of those so cell Q7 reads like :
1.1IP, 3H, 0ER, 0BB, 2K
?

thanks for your help!

 

[tweedle]

Wrapping them in CONCATENATE, I believe...

=CONCATENATE(
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 13, FALSE) & "IP," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 14, FALSE) & "H," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 16, FALSE) & "ER," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 17, FALSE) & "BB," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 19, FALSE) & "K" )

 

[tweedle]

Additional variations:

=
CONCATENATE(
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 13, FALSE) , "IP," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 14, FALSE) , "H," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 16, FALSE) , "ER," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 17, FALSE) , "BB," ,
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 19, FALSE) , "K" )

=
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 13, FALSE) & "IP," &
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 14, FALSE) & "H," &
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 16, FALSE) & "ER," &
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 17, FALSE) & "BB," &
VLOOKUP($A$2, LAST7DAYSP!B3:W17, 19, FALSE) & "K"