Dynamic Vlookup
Hi,
Need your help on this. I have some metrics data for five months in data tab.
What i need is that when I select a particular month through a drop down list, metrics should display the corresponding data for the selected month.
What i need is a vlookup function that uses the col index number dynamically based on the selected month.
Thanks in advance
INDEX / MATCH / MATCH is more robust for this kind of thing. Here it is with the raw ranges, in B3:
=INDEX(Data!$B$2:$M$5, MATCH($A3, Data!$A$2:$A$5, 0), MATCH($B$1, Data!$B$1:$M$1, 0))
...just copy that down.
One of the fun benefits of INDEX/MATCH is when you couple its use with Named Ranges. I color coded your sheet to make the named range possibilities more evident.
If you named the green section TABLE, the blue section MONTHS, the pink section METRICS, then you could change the formula above to a wonderfully readable:
=INDEX(Table, MATCH($A3, Metrics, 0), MATCH($B$1, Months, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks