Vlookup for Multiple Rows Ranges

[JayDeeExcel]

Hello Friends,

I am trying to write a vlookup formula on a sheet that contains large data for all the months. Every month has same number of consumers.

Consumer No.	Month	Value
1	Jan	abc
2	Jan	def
3	Jan	ghi
1	Feb	jkl
2	Feb	mno
3	Feb	pqr
1	Mar	stu
2	Mar	vwx
3	Mar	isp
1	Apr	psi
2	Apr	hgf
3	Apr	abs

<tbody>
</tbody>

So instead of telling excel the starting and ending range for a particular month in a hard coded manner, I want to write a formula that translates into month's range depending upon the selection of month from user. For e.g. lookup range for feb should be A4:C6.

I tried to write different formulas but haven't succeeded as yet. I used address, index and cell for this in vlookup. For e.g. =VLOOKUP(K1,"$A$1"&":"&CELL("address",INDEX(B1:B566488,MATCH(L3,B1:B566488,0),0)),2,0)

where K1 is customer id to look for and L3 contains the name of month

It translates into right range but excel returns #Value error.

I have used many methods but in vain so far. Is it something to do with excel returning text instead of range or something else?

Please help with formula and if possible with concept.

Thanks!

 

[Eric W]

Welcome to the forum.

Try:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Consumer	No.	Month	Value							2
2	1	Jan	abc
3	2	Jan	def									Mar	vwx
4	3	Jan	ghi
5	1	Feb	jkl
6	2	Feb	mno
7	3	Feb	pqr
8	1	Mar	stu
9	2	Mar	vwx
10	3	Mar	isp
11	1	Apr	psi
12	2	Apr	hgf
13	3	Apr	abs

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Array Formulas Cell Formula M3 {=INDEX(C2:C13,MATCH(K1&"|"&L3,$A$2:$A$13&"|"&$B$2:$B$13,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

That's an array formula, make sure to use Control+Shift+Enter when putting it in the formula bar.

 

[JayDeeExcel]

Thank you Eric for an elegant solution.

Can you recommend me a good book to read about array formulas with match and index? or any other resource?

Best Regards!

 

[Eric W]

I'm glad that works for you!

As far as Excel resources, I'm largely self-taught, so I can't really recommend anything from personal experience. However, there is an absolute wealth of information online, and books and videos, depending on your personal learning style.

The Mr. Excel books are generally well-respected, and here's one specifically about array formulas:

Excel, Excel 2010, Excel 2013, Microsoft Excel


As far as other resources, hiker95 has compiled an incredible list pointing to web links or YouTube, or search terms:

https://www.mrexcel.com/forum/excel...best-way-learn-visual-basic-applications.html

Go to the last post in that thread and scan the descriptions for something that matches what you want to study.

Good luck, and feel free to come back if you have any more questions!