An intelligent vlookup formula !

[gaftalik]

Hi,
we know that the vlookup formula look this way :
vlookup(lookup value;table array;col_index_num;range lookup)

if in a table i have 50 columns, shall i count all the columns to see the nbr of the column from which i need to return the value accordingly ,starting from the first column of the table or is there any inteligent formula which by clicking on the needed column itself will exempt me from counting ?!

2) where is the mistake in formula R3 which shall return the same answer as P3?

I appreciate your help , thank you

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3												TOTAL
4			U	T	U	T	U	T	U	T		U	T			U	T
5		A	2	3	4	5	6	7	8	3		20	18		A	20		#N/A
6		B	21	1	34	5	33	12	14	2		102	0		B	102
7		C	12	5	7	8	9	0	9	7		37	0
8		D	3	8	5	12	7	9	3	6		18	0
9
10
11
Sheet1

 

[Aladin Akyurek]

=INDEX($L$5:$M$8;MATCH(O5;$B$4:$B$8;0);MATCH("U";$L$4:$M$4;0))

 

[gaftalik]

My good friend,
i tried it and the result was 102 as P6 instead of 20 as P5
is there something wrong?

 

[Aladin Akyurek]

My good friend,
i tried it and the result was 102 as P6 instead of 20 as P5
is there something wrong?

Try:

=INDEX($L$5:$M$8,MATCH(O5,$B$5:$B$8,0),MATCH(P$4,$L$4:$M$4,0))

Replace , with ;

Book3
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3											TOTAL
4		U	T	U	T	U	T	U	T		U	T			U	T
5	A	2	3	4	5	6	7	8	3					A	20
6	B	21	1	34	5	33	12	14	2					B	102
7	C	12	5	7	8	9	0	9	7
8	D	3	8	5	12	7	9	3	6
9
Sheet1

 

[gaftalik]

It worked nice, thank you you are always there , you are always the solution !

As to my first question defining the column number by other formula in the vlookup formula instead of counting them , do you think it is possible ?

 

[Aladin Akyurek]

...As to my first question defining the column number by other formula in the vlookup formula instead of counting them , do you think it is possible ?

That would not be a problem if the row 4 of your data would consist of distinct items. It's of course possible to use just the L4:M4 part, but the formula would be rather convoluted.

 

[gaftalik]

I see..

thanks for your concern