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.

[TABLE="width: 224"]
<tbody>[TR]
[TD]Consumer No.[/TD]
[TD]Month[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jan[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jan[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jan[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Feb[/TD]
[TD]jkl[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Feb[/TD]
[TD]mno[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Feb[/TD]
[TD]pqr[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mar[/TD]
[TD]stu[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mar[/TD]
[TD]vwx[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mar[/TD]
[TD]isp[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apr[/TD]
[TD]psi[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apr[/TD]
[TD]hgf[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apr[/TD]
[TD]abs[/TD]
[/TR]
</tbody>[/TABLE]

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
Consumer	No.	Month	Value
Jan	abc
Jan	def	Mar	vwx
Jan	ghi
Feb	jkl
Feb	mno
Feb	pqr
Mar	stu
Mar	vwx
Mar	isp
Apr	psi
Apr	hgf
Apr	abs

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M3[/TH]
[TD="align: left"]{=INDEX(C2:C13,MATCH(K1&"|"&L3,$A$2:$A$13&"|"&$B$2:$B$13,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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!