VLOOKUP with condition

[AnWeNeT]

Well I have one question:

I have a master list (I call it that way) of ID, User name, Post Office (email world) - it is on sheet1. Now on my sheet2 I am trying to, no I am doing =VLOOKUP(A:A,sheet1!users,2,FALSE). So I match ID in A:A on sheet2 (Ohh yeah sheet2 has IDs listed - top to bottom) with users list from sheet1. It works... but there is a little twist to the story.... Multiple Post Offices allow the same ID (but unique within Post Office), therefore my VLOOKUP function picks first matching name from the top. Now in some cases IDs do not match the names... In this case I am asking administrators to enter Post Office name in sheet2 (B2). I am thinking that my formula should match ID only if B2 matches the users Post Office (C column in sheet1!users).
I know I am sitting at front of very powerful application.. but functions and programming are not my strong side.

Thanks for any suggestions and ideas.

Desperate
Andrew.

Sounds like you need to concatenate the ID and PostOffice so that you get unique ID & Post office combinations & then do your vlookup based on your new concatenated field.

[Chris Davison]

On 2002-02-28 08:11, AnWeNeT wrote:
Well I have one question:

I have a master list (I call it that way) of ID, User name, Post Office (email world) - it is on sheet1. Now on my sheet2 I am trying to, no I am doing =VLOOKUP(A:A,sheet1!users,2,FALSE). So I match ID in A:A on sheet2 (Ohh yeah sheet2 has IDs listed - top to bottom) with users list from sheet1. It works... but there is a little twist to the story.... Multiple Post Offices allow the same ID (but unique within Post Office), therefore my VLOOKUP function picks first matching name from the top. Now in some cases IDs do not match the names... In this case I am asking administrators to enter Post Office name in sheet2 (B2). I am thinking that my formula should match ID only if B2 matches the users Post Office (C column in sheet1!users).
I know I am sitting at front of very powerful application.. but functions and programming are not my strong side.

Thanks for any suggestions and ideas.

Desperate
Andrew.

Hi Andrew,

My Mum works for Consignia and continually uses the word "desperate", so any fellow employees have my sympathies : can you post some concrete examples of your data or email me an example, I wouldn't mind taking a look at it, if it's not too late, for you.

novulari@hotmail.com

cheers
Chris

[Aladin Akyurek]

On 2002-02-28 10:41, Chris Davison wrote:

Hi Andrew,

My Mum works for Consignia and continually uses the word "desperate", so any fellow employees have my sympathies : can you post some concrete examples of your data or email me an example, I wouldn't mind taking a look at it, if it's not too late, for you.

novulari@hotmail.com

cheers
Chris
[img]/board/images/smiles/icon_smile.gif[/img]

Andrew,

I'm with Chris.

It would help him and others if you would post a small sample of your data per sheet. If inclined to do so, select an empty cell, type =, select 10 rows worth of data, hit F9, and copy what you see and post it along with the range it occupies and expected results.

Aladin

[AnWeNeT]

I think I have figured it out but I got another challenge:
I can use one of 2 formulas:
=CONCATENATE(B9,MID(C2,1,LEN(C2)))
or
=CONCATENATE(B12,C2)
and both of them do the job, but how do I prevent C2 from changing when I paste this into cell below? I want C2 to stay there.. I want to reference C2 from top to the bottom in column A.
any ideas?
Thanks

[Aladin Akyurek]

On 2002-02-28 11:34, AnWeNeT wrote:
I think I have figured it out but I got another challenge:
I can use one of 2 formulas:
=CONCATENATE(B9,MID(C2,1,LEN(C2)))
or
=CONCATENATE(B12,C2)
and both of them do the job, but how do I prevent C2 from changing when I paste this into cell below? I want C2 to stay there.. I want to reference C2 from top to the bottom in column A.
any ideas?
Thanks

Freeze it: $C$2.

[AnWeNeT]

Thanks a lot. It worked... I can go home.