I am trying to figure out if there is a way to write a formula such that the Table_Array of a vLookup is based on a value from another column. Basically, I want the Vlookup to search on another sheet in a certain column. The column that should be searched will vary but is identified in the cell to the left of the formula.
For example, here is what I want my formula to look like when the value in the preceding column is 138:
=IF(RC[-1] <> "",IFERROR(IF(VLOOKUP(RC14,'Marital Status by Country'!C**138**,1,FALSE)=RC14,"GOOD","ERROR"),"ERROR"),"GOOD")
However, I do not want to hardcode 138. I want the formula to look in the cell directly to the left (RC[-1]) and insert whatever value is there. I have not been able to figure out the syntax to make this happen. It always evaluates to an error when I try things like this:
=IF(RC[-1] <> "",IFERROR(IF(VLOOKUP(RC14,'Marital Status by Country'!C **& RC[-1]**,1,FALSE)=RC14,"GOOD","ERROR"),"ERROR"),"GOOD")
Spreadsheet Visual
Thanks for any advice you can give.
INDEXorCHOOSECOLSfunctions. You should hard-code a range rather than using a whole column reference. For example, in a range that has a maximum number of rows of 1000, something like=INDEX(R1:R1000,,RC[-1])or if you have 365:=CHOOSECOLS(R1:R1000,RC[-1])