find the column

[sksanjeev786]

Hi Team,

I need a column ref. as per below yellow highlighted cell

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		2018					2019					2020					2021
3	Column	Wave 1	Wave 2	Wave 3	Wave 4		Wave 1	Wave 2	Wave 3	Wave 4		Wave 1	Wave 2	Wave 3	Wave 4		Wave 1	Wave 2	Wave 3
4		2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
5
6
7				2020			2021
8				Wave 2	Wave 3		Wave 2	Wave 3
9				3	4		3	4
10
11		Output Need		13	14		18	19
12
13
Sheet2

Cell Formulas
Range	Formula
D9:E9,G9:H9	D9	=MATCH(D$8,$A$3:$S$3,0)

Thanks in advance
Sanjeev

 

[offthelip]

Try this; Note you will need to put 2020 in E7 and 2021 in H7. ( it get very difficult to pick the cell next to a blank cell with formula)

=MATCH(D7,$A$2:$S$2,0)+MATCH(D8,OFFSET($A$3,0,MATCH(D7,$A$2:$S$2,0)-1,1,4),0)

 

[sksanjeev786]

Try this; Note you will need to put 2020 in E7 and 2021 in H7. ( it get very difficult to pick the cell next to a blank cell with formula)

=MATCH(D7,$A$2:$S$2,0)+MATCH(D8,OFFSET($A$3,0,MATCH(D7,$A$2:$S$2,0)-1,1,4),0)

Thank you so much it works for me.

But there is some instance we don't have year data in the row num2 we will just Wave 1, Wave 2 reapltly like (row no 3)

so do we have any other option we can give an array to no Row 3

I know it bit tricky but just need some idea on this

Thanks in advance.

Regards,
Sanjeev

 

[offthelip]

But there is some instance we don't have year data in the row num2 we will just Wave 1, Wave 2 reapltly like (row no 3)

So how would you expect anybody even a human to work out how to match it

 

[sksanjeev786]

So how would you expect anybody even a human to work out how to match it

Hi,

For example, i have Wave 1 and Wave 2 multiple columns with the same name but with match function, I will get 1 and 2 only so do we have any other option where we can get actual column with the formula.

i know its challenging but just wanted to check with you and team

Regards,
Sanjeev

 

[offthelip]

I don't think I understand your question. In the formula I gave you the bit which occurs twice:

MATCH(D7,$A$2:$S$2,0)

is looking at row 7 with the years in it. This is what is selecting which column to start looking for the Wave 1 and Wave 2 etc. SO unless you have data in both rows you can't find the combination of year and wave value. I don't see any way round that fact. If you just look for wave 1, you have got 4 examples in you data , how do you know which one you want?? The answer is you don't, so it is impossible to select one of them without more information.

 

[sksanjeev786]

I don't think I understand your question. In the formula I gave you the bit which occurs twice:

MATCH(D7,$A$2:$S$2,0)

is looking at row 7 with the years in it. This is what is selecting which column to start looking for the Wave 1 and Wave 2 etc. SO unless you have data in both rows you can't find the combination of year and wave value. I don't see any way round that fact. If you just look for wave 1, you have got 4 examples in you data , how do you know which one you want?? The answer is you don't, so it is impossible to select one of them without more information.

I don't think I understand your question. In the formula I gave you the bit which occurs twice:

MATCH(D7,$A$2:$S$2,0)

is looking at row 7 with the years in it. This is what is selecting which column to start looking for the Wave 1 and Wave 2 etc. SO unless you have data in both rows you can't find the combination of year and wave value. I don't see any way round that fact. If you just look for wave 1, you have got 4 examples in you data , how do you know which one you want?? The answer is you don't, so it is impossible to select one of them without more information.

Hello Sir,

Sorry for the delay i was suffering from Fever.

Now all is well

So basically I need column Reff. as per Row number 2 column number

book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Column	Wave 1	Wave 2	Wave 3	Wave 4		Wave 1	Wave 2	Wave 3	Wave 4		Wave 1	Wave 2	Wave 3	Wave 4		Wave 1	Wave 2	Wave 3
2	As it is column	2	3	4	5		7	8	9	10		12	13	14	15		17	18	19
Sheet1

 

[offthelip]

Your data needs to look like this:

 

[sksanjeev786]

Your data needs to look like this:View attachment 46985

Hello Sir,

Thanks again for checking in this

So I have observed in my other sheet most of the column refer are as per post #8 so just wanted to check do we have any other option so that I can get column ref. as per row num 2

I know it is a bit tricky but let me know if we have some alternate things

Thanks in advance
Sanjeev

 

[offthelip]

with your data as shown in post#7, How are you going to tell which year each set of four waves is the one you want?? There is no year shown anywhere