In VLookup / Hlookup function

I want to search from column C and then display the corresponding
value from Column A

Example.....

A B C
5 10 15
6 11 16
9 14 19

I select 16 (Column C) and my answer should be 6 (Column A)...

Now i want to write as follows =vlookup( E3, B3:D6, 1, False/true) ,
but by default when i enter the value in the cell E3, it is searching
in Column A....But i want it to search in the Column C...


how to do it....

PS : it might sound quite stupid , i would just like to know that does
the symbol "$" mean in the array declaration $B$6:$D$10.

Thanks & Regards
Bodhisatya Sen

Please reply at :

Hi

See also the response to your later posting.

The $ signs in formulae make the reference Absolute as opposed to Relative.
$B$6:$B$10 as a range will remain constant as you copy the formula down a
column, or across a row.
If it were B6:B10 then as you copy down it would alter to b7:B11, B8:B12
etc.
If you copied across it would change to C6:C10, D6:D10 etc.

You can choose to just anchor the column by making it Absolute and leave the
row relative as in $B6.
Conversely you can make the row Absolute while making the column relative as
in B$6

Vlookup always looks in the first column of a lookup range to find the
match. That cannot be altered.
Instead use Index and Match

For your example with data in A2:C4
=INDEX(A2:A4,MATCH(E3,C2:C4,0))

For your future postings, this is not the best Newsgroup for you for this
type of question. This newsgroup is read mainly by those seeking VBA
solutions. You would be better posting to
microsoft.public.worksheet.functions

--
Regards
Roger Govier

"getu32" wrote in message
...
In VLookup / Hlookup function

I want to search from column C and then display the corresponding
value from Column A

Example.....

A B C
5 10 15
6 11 16
9 14 19

I select 16 (Column C) and my answer should be 6 (Column A)...

Now i want to write as follows =vlookup( E3, B3:D6, 1, False/true) ,
but by default when i enter the value in the cell E3, it is searching
in Column A....But i want it to search in the Column C...


how to do it....

PS : it might sound quite stupid , i would just like to know that does
the symbol "$" mean in the array declaration $B$6:$D$10.

Thanks & Regards
Bodhisatya Sen

Please reply at :

__________ Information from ESET Smart Security, version of virus
signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hi,
Vlookup looks in the first column try this instead

=INDEX(A1:A6,MATCH(E3,C1:C6,0))

"getu32" wrote:

In VLookup / Hlookup function

I want to search from column C and then display the corresponding
value from Column A

Example.....

A B C
5 10 15
6 11 16
9 14 19

I select 16 (Column C) and my answer should be 6 (Column A)...

Now i want to write as follows =vlookup( E3, B3:D6, 1, False/true) ,
but by default when i enter the value in the cell E3, it is searching
in Column A....But i want it to search in the Column C...


how to do it....

PS : it might sound quite stupid , i would just like to know that does
the symbol "$" mean in the array declaration $B$6:$D$10.

Thanks & Regards
Bodhisatya Sen

Please reply at :
.