# An intelligent vlookup formula !

#### gaftalik

##### Well-known Member
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
=INDEX(\$L\$5:\$M\$8;MATCH(O5;\$B\$4:\$B\$8;0);MATCH("U";\$L\$4:\$M\$4;0))

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

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

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 ?

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.

I see..

Replies
6
Views
298
Replies
1
Views
116
Replies
5
Views
191
Replies
9
Views
595
Replies
5
Views
463

1,218,690
Messages
6,143,940
Members
450,516
Latest member
shironokuro

### 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.

### Which adblocker are you using?

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

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