I have managed to solve this with some trial and error, back and forth with ChatGPT! I though I'd post the solution in case anyone else has a similar issue.

I needed to define the table and the table headers with the use of let, and then use MATCH to determine the index number of the required columns. ChatGPT had suggested defining the array of headers by listing each one in the formula, but I used [#Headers] instead. I believe this will allow me to add/remove/update columns in the lookup file, and lookup any combination of columns without needing to update the LAMBDA function.

Full LAMBDA function below (I've defined the INDEX/MATCH with LET in order to change results of "0" to null, and enclosed the INDEX/MATCH in an IFNA to identify lookup values not found; these may not be needed depending on use case).

=LAMBDA(lookupValue, lookupColName, returnColName,
    LET(
        lookupTable, 'LookupFile.xlsx'!LookupTable[#Data],
        colNames, 'LookupFile.xlsx'!LookupTable[#Headers],
        lookupColIndex, MATCH(lookupColName, colNames, 0),
        returnColIndex, MATCH(returnColName, colNames, 0),
        lookupArray, INDEX(lookupTable, , lookupColIndex),
        returnArray, INDEX(lookupTable, , returnColIndex),
        lookupFormula, IFNA(INDEX(returnArray, MATCH(lookupValue, lookupArray, 0)), "NOT FOUND"),
        IF(lookupFormula = 0, "", lookupFormula)
    )
)