Find Column Number

[Zaigham]

Hi all,

In the below example (actual table is much larger) I want to pull the column number of 3500 at 3E. With match function I have pulled the row number i.e. 5 but I am unable to extract the column number of 3500.

Excel Workbook
	B	C	D	E	F	G	H
2	BPS	0	1	2	3	4	5
3	1E	2,970	3,060	3,150	3,240	3,330	3,420
4	1P	4,345	4,490	4,635	4,780	4,925	5,070
5	2E	3,035	3,135	3,235	3,335	3,435	3,535
6	2P	4,855	5,015	5,175	5,335	5,495	5,655
7	3E	3,140	3,260	3,380	3,500	3,620	3,740
8	3P	5,020	5,210	5,400	5,590	5,780	5,970
9	4E	3,240	3,380	3,520	3,660	3,800	3,940
10	4P	5,180	5,400	5,620	5,840	6,060	6,280
11	5E	3,340	3,500	3,660	3,820	3,980	4,140
12	5P	5,340	5,595	5,850	6,105	6,360	6,615
13	*	*	*	*	*	*	*
14	*	BPS	3E	*	ROW	5	*
15	*	PAY	3500	*	COLUMN	?	*
Sheet1

Please help.

 

[VoG]

Try like this

Excel Workbook
	B	C	D	E	F	G	H
2	BPS	0	1	2	3	4	5
3	1E	2,970	3,060	3,150	3,240	3,330	3,420
4	1P	4,345	4,490	4,635	4,780	4,925	5,070
5	2E	3,035	3,135	3,235	3,335	3,435	3,535
6	2P	4,855	5,015	5,175	5,335	5,495	5,655
7	3E	3,140	3,260	3,380	3,500	3,620	3,740
8	3P	5,020	5,210	5,400	5,590	5,780	5,970
9	4E	3,240	3,380	3,520	3,660	3,800	3,940
10	4P	5,180	5,400	5,620	5,840	6,060	6,280
11	5E	3,340	3,500	3,660	3,820	3,980	4,140
12	5P	5,340	5,595	5,850	6,105	6,360	6,615
13
14	3		3500
15	3E
Sheet3

 

[Zaigham]

Try like this

Excel Workbook
	B	C	D	E	F	G	H
2	BPS	0	1	2	3	4	5
3	1E	2,970	3,060	3,150	3,240	3,330	3,420
4	1P	4,345	4,490	4,635	4,780	4,925	5,070
5	2E	3,035	3,135	3,235	3,335	3,435	3,535
6	2P	4,855	5,015	5,175	5,335	5,495	5,655
7	3E	3,140	3,260	3,380	3,500	3,620	3,740
8	3P	5,020	5,210	5,400	5,590	5,780	5,970
9	4E	3,240	3,380	3,520	3,660	3,800	3,940
10	4P	5,180	5,400	5,620	5,840	6,060	6,280
11	5E	3,340	3,500	3,660	3,820	3,980	4,140
12	5P	5,340	5,595	5,850	6,105	6,360	6,615
13
14	3		3500
15	3E
Sheet3

Thanks, but result of this formula comes to 3500. Actually 3E and 3500 are the input values by the user. I need to know the row number of 3E which is 5 and the position of 3500 i.e., column No.

 

[VoG]

Sorry, I misunderstood. Try like this

Excel Workbook
	A	B	C	D	E	F	G	H
1
2		BPS	0	1	2	3	4	5
3		1E	2,970	3,060	3,150	3,240	3,330	3,420
4		1P	4,345	4,490	4,635	4,780	4,925	5,070
5		2E	3,035	3,135	3,235	3,335	3,435	3,535
6		2P	4,855	5,015	5,175	5,335	5,495	5,655
7		3E	3,140	3,260	3,380	3,500	3,620	3,740
8		3P	5,020	5,210	5,400	5,590	5,780	5,970
9		4E	3,240	3,380	3,520	3,660	3,800	3,940
10		4P	5,180	5,400	5,620	5,840	6,060	6,280
11		5E	3,340	3,500	3,660	3,820	3,980	4,140
12		5P	5,340	5,595	5,850	6,105	6,360	6,615
13
14		3500		6
15		3E		7
16
Sheet3

 

[Zaigham]

Yes, yes. You have gave me a fantastic idea.
Thank you very much Peter.

Sincerely,
Zaigham