I am attempting to use this command
=LOOKUP(H7644,$A1:$A7643,$B1:$B7643)&" WITH E D/G BACKUP" in order to return
a necessary result. When H7644 is entered as text or a number, the return
yields what I want. If H7644 contains something like
=MID(A7644,1,LEN(A7644)-2), nothing happens. I'm assuming this needs to be a
named range or something?

I need this to encompass another 500 rows or so (the $A1:$A7643,$B1:$B7643
range will stay the same, just the H cell reference will increase by an
increment for each additional row).

Anyone have any idea how to get this to work?

I'm using Excel 2003.

Thanks in advance.