how to do this vlookup
 

hi all,
my first file
==========

(a) (b) (c) (d)
sno name pincode area

1 arun 123456 *
2 kumar 123457 *
3 raja 123458 *

note:
====
* how to get area from my second file (like vlook up)
conditions:
========
with out change fields order in both file.

my second file
===========

(a) (b)
area pincode

abc 123456
bcd 123458
cde 123457


my third file
==========

(a) (b) (c) (d)
sno name area pincode

1 arun * 123456
2 kumar * 123457
3 raja * 123458


note:
====
* how to get area from my second file (like vlook up)
conditions:
========
with out change fields order in both file.


pls anyone help this questions . i am awaiting for urs reply.


Regards,
vinci

how to do this vlookup
 

Obviously you can't use Vlookup since what you're searching for is in the
second column, and you want it to return whats in the first column?

I use =INDEX(MATCH())

so say =INDEX($A$2:$A$100,MATCH(C2,$B$2:$B$100,0))

This will find which row the contents of C2 are (First file, 123456) in the
array in column B (obviously change this reference to column B in your second
file) and from your example this will find the number (123456) in the first
cell (B2) and return the value 1.
The index then looks at the first column ("A2:A100") and return the first
value, which is abc.


"vinci" wrote:

hi all,
my first file
==========

(a) (b) (c) (d)
sno name pincode area

1 arun 123456 *
2 kumar 123457 *
3 raja 123458 *

note:
====
* how to get area from my second file (like vlook up)
conditions:
========
with out change fields order in both file.

my second file
===========

(a) (b)
area pincode

abc 123456
bcd 123458
cde 123457


my third file
==========

(a) (b) (c) (d)
sno name area pincode

1 arun * 123456
2 kumar * 123457
3 raja * 123458


note:
====
* how to get area from my second file (like vlook up)
conditions:
========
with out change fields order in both file.


pls anyone help this questions . i am awaiting for urs reply.


Regards,
vinci

how to do this vlookup
 

thank u sir,
my first file problem was solve. but do the third
one file.

regards,
vinci






On Mar 14, 7:55 pm, PaulW wrote:
Obviously you can't use Vlookup since what you're searching for is in the
second column, and you want it to return whats in the first column?

I use =INDEX(MATCH())

so say =INDEX($A$2:$A$100,MATCH(C2,$B$2:$B$100,0))

This will find which row the contents of C2 are (First file, 123456) in the
array in column B (obviously change this reference to column B in your second
file) and from your example this will find the number (123456) in the first
cell (B2) and return the value 1.
The index then looks at the first column ("A2:A100") and return the first
value, which is abc.

"vinci" wrote:
hi all,
my first file
==========

(a) (b) (c) (d)
sno name pincode area

1 arun 123456 *
2 kumar 123457 *
3 raja 123458 *

note:
====
* how to get area from my second file (like vlook up)
conditions:
========
with out change fields order in both file.

my second file
===========

(a) (b)
area pincode

abc 123456
bcd 123458
cde 123457

my third file
==========

(a) (b) (c) (d)
sno name area pincode

1 arun * 123456
2 kumar * 123457
3 raja * 123458

note:
====
* how to get area from my second file (like vlook up)
conditions:
========
with out change fields order in both file.

pls anyone help this questions . i am awaiting for urs reply.

Regards,
vinci