Using VLOOKUP to apply an ARRAY formula

Hi all,

I have this array formula which I will be using to sum an overall total:

=SUM(IF(ISTEXT(P8:BX182),(LEFT(P8:BX182,1)="H")*(0&TRIM(MID(P8:BX182,3,255)))))

What I would also like to do is run a VLOOKUP formula in another sheet (lets call it sheet2) where values in column A (name) are the reference for the VLOOKUP. I then just want to return a result for the row starting with that name in sheet1.

Frankly VLOOKUP confuses me a little, and without the help of the mighty Ron the above formula wouldn't even exist!

Any help greatfully received!