An intelligent vlookup formula !

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
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
ABCDEFGHIJKLMNOPQRST
1
2
3TOTAL
4UTUTUTUTUTUT
5A234567832018A20#N/A
6B21134533121421020B102
7C125789097370
8D385127936180
9
10
11
Sheet1
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
My good friend,
i tried it and the result was 102 as P6 instead of 20 as P5
is there something wrong?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
gaftalik said:
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
BCDEFGHIJKLMNOPQ
3TOTAL
4UTUTUTUTUTUT
5A23456783A20
6B2113453312142B102
7C125789097
8D385127936
9
Sheet1
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
gaftalik said:
...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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,815
Messages
5,598,259
Members
414,220
Latest member
Nations

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top