Referring to Multiple Columns w/In Same Table in a Macro

I have some complicated excel formulas (nested conditionals, etc.) that I'm hoping to optimize w/VBA. I use tables because the data sets are dynamic. My goal is to be able to replace some complicated functions with macros that pull in table data into an array, execute the worksheet functions, and spit the results back out into the corresponding cell. My specific problem is that I'm curious if there's a way within VBA to refer to a column within the same record/row on the same table, in the same way that Access' version of SQL and basic Excel functions permit.

In excel, one simple uses brackets and the @ sign, like this example of an excel formula in a column titled "Duplicate":

=IF(AND(COUNTIFS([NAME],[@[NAME]],[LOCATION],[@[LOCATION]])>1,NOT([@[IGNORE_RECORD]]="NO")),"DUPLICATE","OK")

Here, the table knows would know that for, say, a row representing a person named John Smith, it needs to look at the cell for "Ignore_Record," "Name," and "Location," in the same row as John Smith's name and populate the result in the column "Duplicate."

My questions for you all are

(1) whether there are any resources online or general tips about working with VBA arrays, listobjects, etc. to accomplish this same thing--ie, for relative references that allows the use of named ranges (I've found some solutions online using offsets, but these seem cumbersome and not scalable for future use, although I'd love any feedback if this impression is misguided)

(2) if you think this is better achieved as function, rather than a macro incorporating worksheet functions?

(Very green at VBA but decent at SQL and very proficient in non-VBA excel). Thank you!

I have a series of cascading conditionals that make the calculating take ages, due to the complexity of the functions, the number of them, and the number of records/rows. For example, one column might check if event X is type A or B, which another column relies on determine what date X must be completed on, which looks up who should be assigned to event X based on date from another table, etc., etc. I just can't figure out how to use the name of a column in a worksheet function instead of an absolute reference. I can't just use the @ sign in VBA, can I? Originally I had a macro that was copy/pasting the formulas instead of executing the functions directly but it was taking over 10 minutes for a CSE/array formula so I stopped it.

I think I've maximized what I can do with basic excel (shunning the dreaded lookups in favor of index/match, dynamic ranges, setting calculations to update manually rather than automatically, etc.), and I was hoping to migrate them into VBA so I could just hit a button and wham, do it all at once and not have this monster of a workbook. The tool metaphor resonates. I could do it with Access in SQL, but that seemed like too large a tool, and the basic excel is a tad slow. I thought VBA/Excel might be something of a middle-ground, just right, goldilocks type of tool, and hoping to learn more VBA generally.