Vlookup

G

getu32

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 : (e-mail address removed)
 
R

Roger Govier

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 said:
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 : (e-mail address removed)

__________ 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
 
E

Eduardo

Hi,
Vlookup looks in the first column try this instead

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top