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’!C138,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”)
Thanks for any advice you can give.
