Vlookup Match Problem

H

Hardeep Kanwar

Hello Sir,

I have a data in 2 Sheets

Sheet2
Phone Number
26423694
4030609646
4069934154
8025350322
8025350322
8472258299
8474204342
8856237979
9000556977
9000556977
9000766578
9003293914
9008354129
9008416704
9008448709
9018098525
9030161071
9030353805
9030481623
9030483779
9030483779

Sheet3

Phone Number Preferred Campus
2331772 Bangalore
22247264 Chennai
24030404 Bangalore
26423694 Chennai
4030609646 Hyderabad
4069934154 Hyderabad
4442992167 Chennai
8025350322 Bangalore
8025350322 Punjab
8472258299 Hyderabad
8474204342 Hyderabad
8856237979 Vishakapatnam
9000091997 Hyderabad
9000556977 Hyderabad
9000556977 Pune
9000766578 Hyderabad
9003293914 Chennai
9008354129 Bangalore
9008416704 Bangalore
9008448709 Hubli
9018098525 Bangalore
9030161071 Hyderabad
9030353805 Hyderabad
9030481623 Hyderabad
9030483779 Hyderabad
9030483779 Delhi

http://www.microsoft.com/office/com...ublic.excel.worksheet.functions&lang=en&cr=US


Right Now i am using this
OFFSET(Sheet3!$B$1,LARGE((A$2=Sheet3!A$2:A$27)*ROW(Sheet3!A$2:A$27),ROW(A1))-1,0)

But i got #Ref

Confirm with CTRL+SHIFT+ENTRE

Where i was Wrong

Thanks in advance

Hardeep kanwar
 
H

Hardeep Kanwar

Sir,

My this Question is totally different from my Previous Query

Regards

Hardeep kanwar
 
J

JP Ronse

Hi,

{=OFFSET($H$1;LARGE(($A2=$G$2:$G$27)*ROW($G$2:$G$27);ROW($A$1))-1;0)}

I had to change 2 addresses to get it working:

- You have ... LARGE(A$2=...
If you copy this function A$2 will not change to A$3
Change it LARGE($A2=

- You have ROW(A1)
By coping this will also change to A2, AA3, A4
Change this to $A$1 to keep it as fix reference.

Hope this helps.

Wkr,

JP
 
Top