Good afternoon Excel Forum,
I have been struggling with this problem for over a week now and have not been able to figure it out. What I have is a data set that i need to paste into an excel on a frequent basis. The downside to the way this is pasted is that things may not always be in the same place when i paste it. I have been using VLOOKUP to accomplish what i am asking. Here is where the complicated part comes in. This pasted data sometimes has multiple columns. What i am trying to do is have a formula that finds the 2nd column in the data set and starts the range for a VLOOKUP based on where this column is located.
Here is what i have been able to accomplish so far.
This equation finds the 2nd column's start cell within the pasting range.
=ADDRESS(55+(MATCH("Red Tagged",'sheet2'!M55:M77,0)),13)
The name of the second column in the pasting range is "Red Tagged". This equation output the cell that 'Red Tagged' appears in the pasting range. In this case it is $M$69.
Now i want this cell be to be used as the first cell in the range of a VLOOKUP equation. Here is what my VLOOKUP equation currently looks like:
=IFERROR(VLOOKUP("Combos",'sheet2'!M55:N77,2,0),"N/A")
This is how i want the equation to look and work:
=IFERROR(VLOOKUP("Combos",'sheet2'!(ADDRESS(55+(MATCH("Red Tagged",'sheet2'!M55:M77,0)),13)):N77,2,0),"N/A")
But unfortunately the equation does not work like this. It appears that it wont let me have an equation for a cell location within a equation.
Any help or idea of how to make something like this work would be greatly appreciated!
Thanks for taking a look!
Bookmarks