Need same Vlookup formula for use in different scenarios

Hello,
I'm looking for a way to create a vlookup formula where the lookup value can be located in different spots in my data sets and still work.

For example, in the attached I want to lookup the Age Value and pull in the Age Group based on the lookup table. However, the Age Column could shift to different columns based on the scenario, like in the second data set. I want to be able to use the same formula for both cases. I'm thinking an Index Match might work but I can't seem to figure it out.


Thanks so much, this will save me so much time!

Is this what you mean??

In N8, copied down:

=IFERROR(VLOOKUP(INDEX($A$8:$M$20,ROWS(N$8:N8),MATCH("Age",$A$7:$M$7,0)),'Age Table'!$A:$B,2,1),"")

not quite...

=VLOOKUP(INDEX(range of data excluding header, ROWS(address of cell that the formula is IN*),MATCH("Age",range of data headers ONLY,0)),'Age Table'!$A:$B,2,1)

* Pay attention to the $ signs. If the formula is in P14... it should read ROWS(P$14:P14)

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.