Pull data from every 5th column based on dropdown selection

[Twinky]

Hi,
I need to populate a column of cells based on a dropdown list selection at the top of that column. The dropdown list is based on a series of columns located towards the right on the same sheet. Those columns on the right are organized in groups with column headings (BUDGET UPDATE 1, BUDGET UPDATE 2, BUDGET UPDATE 3 etc etc). I have a "NOTES" column within each of the BUDGET UPDATES groups.

I'd like to use a dropdown list with BUDGET UPDATE 1, BUDGET UPDATE 2, BUDGET UPDATE 3 etc etc. to then extract the data in the respective "NOTES" column that corresponds to it

The problem I'm having is that the "NOTES" columns are not consecutive, but are located every fifth column. I have successfully used the INDEX & MATCH to work elsewhere within this workbook but I haven't got a clue how to get the formula to ignore the in-between columns.

If you have any ideas, thanks SO MUCH in advance!

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Budget Update 1						Budget Update 2						Budget Update 3
2	Increase	Notes	Total Oc	OPB	Cause	Increase	Increase	Notes	Total Oc	OPB	Cause	Increase	Increase	Notes	Total Oc	OPB	Cause	Increase
3		NINW						XNNK						UVPT
4		WVNY						PQTH						SZMV
5		QDAT						GNBX						TOEA
6		GCBN						AMIH						ORFS
7		ZLZE						JITT						CQIB
8
9				List
10				Budget Update 1
11				Budget Update 2
12		DropDown		Budget Update 3
13		Budget Update 2
14		NOTES
15		XNNK
16		PQTH
17		GNBX
18		AMIH
19		JITT
20
Sheet2

Cell Formulas
Range	Formula
B15:B19	B15	=INDEX($A$3:$R$7,0,MATCH($B$13,$A$1:$R$1,0)+1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B13	List	=$D$10:$D$12

 

[Twinky]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Budget Update 1						Budget Update 2						Budget Update 3
2	Increase	Notes	Total Oc	OPB	Cause	Increase	Increase	Notes	Total Oc	OPB	Cause	Increase	Increase	Notes	Total Oc	OPB	Cause	Increase
3		NINW						XNNK						UVPT
4		WVNY						PQTH						SZMV
5		QDAT						GNBX						TOEA
6		GCBN						AMIH						ORFS
7		ZLZE						JITT						CQIB
8
9				List
10				Budget Update 1
11				Budget Update 2
12		DropDown		Budget Update 3
13		Budget Update 2
14		NOTES
15		XNNK
16		PQTH
17		GNBX
18		AMIH
19		JITT
20
Sheet2

Cell Formulas
Range	Formula
B15:B19	B15	=INDEX($A$3:$R$7,0,MATCH($B$13,$A$1:$R$1,0)+1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B13	List	=$D$10:$D$12

Thanks so much awoohaw, really appreciate you taking the time to help out.
Your layout seems quite clear to me and I didn't have any problem seeing what you did. However, for some reason the array isn't returning value. It's just blank when I enter the notes and make the dropdown selection.
There is this other odd thing that the array is producing: it's returning a 0 in the cells that are located between the rows where I would enter any data or notes. Here is a screen shot. For clarity, I added a green color to the column where the array is located. The cells that have visible grid lines are the active value lines which would have corresponding notes entered in my columns off to the far left of the sheet.
Any ideas what may be going wrong?

awoohaw​

 

[awoohaw]

I don't know what to say. My suggestion was based on your initial scenario.

Without knowing your data I don't see how I can update the I don't know what your data looks like, pictures are worthless without the underlying formulas and row and column numbers.

Have you pasted my suggestion into a fresh worksheet and then try to build around that?

Unless you post an xl2bb mini sheet (or a plain table paste) of your data and expected results I don't see what can be done.

The only thing that I did not make clear is to question your top row. If those are merged cells (which I abhor) then the match formula may not work correctly. To fix that: unmerge those cells and use the "Center over Selection" method for formatting your group headers.

 

[Twinky]

I don't know what to say. My suggestion was based on your initial scenario.

Without knowing your data I don't see how I can update the I don't know what your data looks like, pictures are worthless without the underlying formulas and row and column numbers.

Have you pasted my suggestion into a fresh worksheet and then try to build around that?

Unless you post an xl2bb mini sheet (or a plain table paste) of your data and expected results I don't see what can be done.

The only thing that I did not make clear is to question your top row. If those are merged cells (which I abhor) then the match formula may not work correctly. To fix that: unmerge those cells and use the "Center over Selection" method for formatting your group headers.

Ok I'll play around a bit and try and resolve. Thanks agian so much for getting me this far, I really appreciate it!