Changing column index number in Vlookup

[Dwyane]

I have number arranged horizontally at the bottom of my spreadsheet and below looksup their percentage value. I want it to be more dynamic. I want the column index number to change if the last value in column A changes. In my example, it should show me 50% 50% because the last number is 0 which belongs to group2(column L). if the last number in column A was say 12,or any number from group 1 then the below lookup would ok.

VLOOKUP(I15,$H$2:$N$9,4)[/CODE
]

 

[Legacy 143009]

How should Excel suppose to know if 0 belongs to Colum L since there is no 0 value in that column?

 

[Alex Blakenburg]

This is my understanding:
1) Percentage
Percentage1 applies if the number is contained in the brackets of group 1 being (22,12,13)
Percentage2 applies if the number is contained in the brackets of group 2 being (2,4,0)
I think you would need a mapping table to support that as shown below.
2) You want to look up the last item entered in Column A

The internal Index-XMatch get the cell with data in column A
The Next Vlookup get the Group no from the Code-Group mapping Table
This converts to "Percentage" & group no (1 or 2)
The next Match gets the column for the Percentage 1 or 2
The outer Vlookup gets the percentage you are after.

20221111 Vlookup Index Match Dwyane.xlsx
	A	B					G	H	I	J	K	L	M	N	O	P	Q
1	22							num	group 1(22,12,13)	count1	Percentage1	group 2(2,4,0)	count2	Percentage2		Code	Group
2	13							1			33%			50%		22	1
3	12							2			33%					12	1
4	12							3								13	1
5	2							4			33%					2	2
6	4							5						50%		4	2
7	0							6								0	2
8								7
9								8
10
11
12
13
14
15									1	2	3	4	5	6	7	8
16									50%	0%	0%	0%	50%	0%	0%	0%
Data

Cell Formulas
Range	Formula
I16:P16	I16	=VLOOKUP(I$15,$H$2:$N$9,MATCH("Percentage" & VLOOKUP(INDEX($A$1:$A$10,XMATCH(TRUE,$A$1:$A$10<>"",0,-1),0),$P$2:$Q$7,2,FALSE),$H$1:$N$1,0),FALSE)

 

[Dwyane]

How should Excel suppose to know if 0 belongs to Colum L since there is no 0 value in that column?

This formula goes into column L

IFERROR(FILTER(G1:G8,(A1:A8=2)+(A1:A8=4)+(A1:A8=0)),"")

. It looks up values in G if the value in column A is 0,4 or 2. So since zero is the last number in A, the column index should dynamically change to 7 which is the column for percentage 2

 

[Dwyane]

This is my understanding:
1) Percentage
Percentage1 applies if the number is contained in the brackets of group 1 being (22,12,13)
Percentage2 applies if the number is contained in the brackets of group 2 being (2,4,0)
I think you would need a mapping table to support that as shown below.
2) You want to look up the last item entered in Column A

The internal Index-XMatch get the cell with data in column A
The Next Vlookup get the Group no from the Code-Group mapping Table
This converts to "Percentage" & group no (1 or 2)
The next Match gets the column for the Percentage 1 or 2
The outer Vlookup gets the percentage you are after.

20221111 Vlookup Index Match Dwyane.xlsx
	A	B					G	H	I	J	K	L	M	N	O	P	Q
1	22							num	group 1(22,12,13)	count1	Percentage1	group 2(2,4,0)	count2	Percentage2		Code	Group
2	13							1			33%			50%		22	1
3	12							2			33%					12	1
4	12							3								13	1
5	2							4			33%					2	2
6	4							5						50%		4	2
7	0							6								0	2
8								7
9								8
10
11
12
13
14
15									1	2	3	4	5	6	7	8
16									50%	0%	0%	0%	50%	0%	0%	0%
Data

Cell Formulas
Range	Formula
I16:P16	I16	=VLOOKUP(I$15,$H$2:$N$9,MATCH("Percentage" & VLOOKUP(INDEX($A$1:$A$10,XMATCH(TRUE,$A$1:$A$10<>"",0,-1),0),$P$2:$Q$7,2,FALSE),$H$1:$N$1,0),FALSE)

Hello Mr. Blakenburg,
I am intrigued by how this works perfectly. Still reading to get a better understanding. Thank you very much

 

[Alex Blakenburg]

You're welcome. Glad we could help.
Let me know if you have questions on how it works.
I forgot to mention that the XMatch has the parameter set (-1) to start looking from the bottom up.