use Formula to make Formula?

I am a total beginner, so please bear with me:

I have two tables which share a unique identifier (customer name).

I want to populate a column in TableA based on a value on TableB. Basically =IF(VLOOKUP(A1,Table2!A1:E9,5,false)="Invoice","Wholesale","Retail") EXCEPT that the value I want to check for the conditional is not on the same row as the lookup value, but on the row below it.

I tried different ideas for creating the result I want, including something like ="Table2!$F$"&(MATCH(A1,Table2!A1:A5)+1) but that sort of thing doesn't work (gives right formula, but as text).

I tried looking this up but I couldn't refine my search parameters well enough to get any useful answer.

Any ideas where to start/look?

I know I could've written a script for this by now but it seemed simple enough to do... it probably is isn't it ;-)

Anyway, I find the need to make formulas like this for small things or one shot data retrieval and it'd be nice to know how to accomplish this using formulas.

(Sorry if I missed the answer somewhere on this site, or the web... it's a really big place)
Thanks