Vlookup Not Work From Different Sheet

[muhammad susanto]

hi all...

how to lookup different sheets:

testing.xlsx
	A	B
1	city	name
2	jakarta	a
3	jakarta	b
4	jakarta	c
5	jakarta	d
6	hongkong	g
7	hongkong	h
8	singapore	x
9	singapore	s
10	singapore	r
11	singapore	t
12	singapore	w
13	singapore	k
data

in sheet data

testing.xlsx
	A	B
1	name	city
2	a	#N/A
3	c
lookup

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(A2,data!$A$2:$B$13,2,FALSE)

my target in sheet lookup
thank for anyone help

susant

 

[Fluff]

Vlookup will happily work across sheets, but your problem is that the lookup values is in the 2nd column & not the 1st.
You will need to use Index & Match

 

[muhammad susanto]

how to use it?

 

[Alex Blakenburg]

I thought you might need that, see below

Book4
	A	B
1	name	city
2	a	jakarta
3	c	jakarta
4	g	hongkong
lookup

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(data!$A$2:$A$13,MATCH(A2,data!$B$2:$B$13,0),0)

 

[muhammad susanto]

hi alex..
i have new problem

testing fix.xlsx
	A	B	C
1	no	city	name
2	1	jakarta	mia
3	2	jakarta	tika
4	3	jakarta	dita
5	4	jakarta	sisca
6	5	hongkong	vita
7	6	hongkong	john
8	7	kuala lumpur	mack
9	8	kuala lumpur	snake
10	9	kuala lumpur	jack
data

testing fix.xlsx
	A	B	C
1	city2	target	shoud be
2	hongkong	vita	vita
3	hongkong	vita	john
4	kuala lumpur	mack	mack
5	kuala lumpur	mack	snake
6	kuala lumpur	mack	jack
7	jakarta	mia
8	jakarta	mia
lookup

Cell Formulas
Range	Formula
B2:B8	B2	=INDEX(data!$C$2:$C$10,MATCH(A2,data!$B$2:$B$10,0),0)

how about if list of name is unique
the formula is not work corect

 

[Alex Blakenburg]

That is a totally different question and quite frankly doesn't seem to make sense.
You seem to want to use a lookup to replicate the original data sheet.

You were using the name to get the city and that seems fine. You now seem to want the reverse ie to lookup on the city and return all the names.

I am in Australia and login off for the night.
If you would like to clarify what you are trying to do perhaps @Fluff, can give you something that works for you.

 

[Fluff]

i have new problem

As it is a totally different question, it needs a new thread. Thanks