prob with vlookup

[imported_unknown]

Hello,

I've got a problem. I've illustrated it with an example below:

Sheet 1
Employee Name Designation
John Manager
Smith Executive
Allan Sales
Smith Sr. Manager
Steven Sales
Ann Receptionist

Sheet 2
Ann Receptionist
Smith Executive
Harris #N/A
Chris #N/A
John Manager
Adams #N/A
Smith Executive
Allan Sales

Let's tale for example there are 2 people in the company by the name of Smith. (executive and Sr. Manager). While doing a vlookup in sheet 2, in both places you will only get the result for designation as 'executive' for Smith. This is because vlookup only picks up the 1st value and matches it against corresponding database.
Is there any way to eliminate this problem or better still, is there any other formula that can be used to match "smith" against "executive" and "smith" against "sr. manager" while comparing data bases? Pls revert back ASAP.

Thanks
Straus

 

[DonkeyOte]

There'll no doubt be ways of doing this using INDEX etc but the simplest way is to make sure you always use unique records if you want to use VLOOKUP - can you not merge A & B and use the merged data as your primary column on your second sheet?

e.g VLOOKUP(A1&" "&B1,DATA,3,FALSE)

where DATA column A is equivalent of A1 & " " & B1 (e.g "Smith Snr Manager")

IF you can't do this suggest you look into Index and also Match.

 

[Aladin Akyurek]

On 2002-10-07 04:40, straus wrote:
Hello,

I've got a problem. I've illustrated it with an example below:

Sheet 1
Employee Name Designation
John Manager
Smith Executive
Allan Sales
Smith Sr. Manager
Steven Sales
Ann Receptionist

Sheet 2
Ann Receptionist
Smith Executive
Harris #N/A
Chris #N/A
John Manager
Adams #N/A
Smith Executive
Allan Sales

Let's tale for example there are 2 people in the company by the name of Smith. (executive and Sr. Manager). While doing a vlookup in sheet 2, in both places you will only get the result for designation as 'executive' for Smith. This is because vlookup only picks up the 1st value and matches it against corresponding database.
Is there any way to eliminate this problem or better still, is there any other formula that can be used to match "smith" against "executive" and "smith" against "sr. manager" while comparing data bases? Pls revert back ASAP.

Thanks
Straus

You need the unique IDs as the first column of the lookup table in Sheet1. You could use for example the employee number or SSN instead of their names or uniquify the names.
This message was edited by Aladin Akyurek on 2002-10-07 05:33

 

[imported_unknown]

Hi,

Thanks so much for your prompt reply. However, my problem is not yet solved. You see, I cannot merge A & B and use the merged data as a primary column in the second sheet because I do not have details of designation in the second sheet. In fact, the designation is exactly what i'm trying to obtain in the 2nd sheet.

Is there no such formula where, if after VLOOKUP there is a duplicate result, the formula should search for and pick up the second result. Can this be done through INDEX or any other formula? Could a macro be created to get the desired result? Pls HELP.

Thanks again,
Straus

On 2002-10-07 05:05, lasw10 wrote:
There'll no doubt be ways of doing this using INDEX etc but the simplest way is to make sure you always use unique records if you want to use VLOOKUP - can you not merge A & B and use the merged data as your primary column on your second sheet?

e.g VLOOKUP(A1&" "&B1,DATA,3,FALSE)

where DATA column A is equivalent of A1 & " " & B1 (e.g "Smith Snr Manager")

IF you can't do this suggest you look into Index and also Match.

 

[DonkeyOte]

The bit I am struggling to understand is that you want to look up Smith say but where column B also equals job type - but what exactly are you trying to look up from your second sheet? (i.e. which column are you picking up?) - my point being why are you finding their job type by searching by name when that's already in Column 2 on your first sheet? Can you elaborate a little for us.

To do as a macro...

DATA = Sheets("Sheet1").range("a1:A1000")
CROW = 2 (say row 2 is your first name)

For each EE in DATA

IF EE = "" THEN EXIT FOR

IF EE = SHEETS("SHEET2").RANGE("A" & CROW) and SHEETS("SHEET1").RANGE("B" & CROW) = SHEETS("SHEET2").RANGE("B" & CROW) then

SHEETS("SHEET1").RANGE("C" & CROW) = SHEETS("SHEET2").RANGE("C" & CROW)

END IF

CROW = CROW + 1

NEXT EE


All this does is when it finds a match between A & B from Sheet1 in Sheet 2 it returns the value in C from Sheet2 to Sheet1.

Is this along the lines of what you want?

(P.S Make sure your names on Sheet2 also start on Row2)

 

[imported_unknown]

Hi,
I'm so sorry to trouble you again. I'll try my best to explain my problem again. Let me illustrate it with another example below ( pertaining to a telephone company ): In sheet 1, I have Various A/c nos with their corresponding telephone nos. ( Pls note that in a particular A/c, there can be more than 1 telephone no. just as in a company there can be more than 1 telephone connection ). In sheet 2, I have a list of some A/c nos. I want to find out which are the telephone nos pertaining to those A/c nos. Now if I do a VLOOKUP in the second sheet, in cases where the A/c ( for eg. 1180040600 ) no. is repeated twice ( as a result of 2 diff tel nos. ), the result will only pick the 1st telephone number ie 0226979383 in both places and leave out 0226990289. How do i get over this problem so that i can get both 0226979383 & 0226990289.

sheet 1
A/c Number Tel Number
1080019977 0226916145
1080032707 0226934762
1080043019 0226992301
1080043670 0226912959
1180039669 0226963518
1180040600 0226979383
1180040600 0226990289
1180044784 0225980569
1180047183 0226970514
1180051623 0226998399
1280049857 0226971612
1280049857 0226971611
1380037984 0225976059
1380040079 0225980070
1380047736 0226970571
1380047736 0226970572
1380049757 0225986515
1480043536 0226964726
1480047040 0226995436
1480049467 0226996871
1580018719 0226916877
1580031019 0226944097
1580031811 0226959703
1580034732 0225977890
1580039715 0226960851
1580039715 0226960850
1580040911 0225950912
1580047213 0225950911
1580047213 0226929151
1580047213 0226970181
1580047213 0226970191
7380055017 0226916890

Sheet 2
A/c Number
1080019977
1080032707
1080043119
1080043670
1180039669
1180040600
1180040600
1180044784
1180047183
1180081623
1280049857
1280049857
1380037984
1380047736
1380047736
1380049757
1380070079
1480043536
1480047040
1480059467
1580018719
1580031019
1580031811
1580039715
1580039715
1580040911
1580040911
1580047213
1580047213
1580047213
1580047213
7380055017

thanks
straus

 

[DonkeyOte]

Straus - so actually sheet 2 is looking at sheet1 to find all of the various phone numbers pertaining to a particular account and then lists them next to the account number? (in more than one column if more than one number per account?)

 

[imported_unknown]

Yep...you got it right. in case there is more than 1 tel no ( eg 2 tel nos )in sheet 1, ( eg 1180040600 ), the A/c number will be repeated twice in sheet 2.

thanks
straus

On 2002-10-07 07:13, lasw10 wrote:
Straus - so actually sheet 2 is looking at sheet1 to find all of the various phone numbers pertaining to a particular account and then lists them next to the account number? (in more than one column if more than one number per account?)

 

[Aladin Akyurek]

On 2002-10-07 07:08, straus wrote:
Hi,
I'm so sorry to trouble you again. I'll try my best to explain my problem again. Let me illustrate it with another example below ( pertaining to a telephone company ): In sheet 1, I have Various A/c nos with their corresponding telephone nos. ( Pls note that in a particular A/c, there can be more than 1 telephone no. just as in a company there can be more than 1 telephone connection ). In sheet 2, I have a list of some A/c nos. I want to find out which are the telephone nos pertaining to those A/c nos. Now if I do a VLOOKUP in the second sheet, in cases where the A/c ( for eg. 1180040600 ) no. is repeated twice ( as a result of 2 diff tel nos. ), the result will only pick the 1st telephone number ie 0226979383 in both places and leave out 0226990289. How do i get over this problem so that i can get both 0226979383 & 0226990289.

sheet 1
A/c Number Tel Number
1080019977 0226916145
1080032707 0226934762
1080043019 0226992301
1080043670 0226912959
1180039669 0226963518
1180040600 0226979383
1180040600 0226990289
1180044784 0225980569
1180047183 0226970514
1180051623 0226998399
1280049857 0226971612
1280049857 0226971611
1380037984 0225976059
1380040079 0225980070
1380047736 0226970571
1380047736 0226970572
1380049757 0225986515
1480043536 0226964726
1480047040 0226995436
1480049467 0226996871
1580018719 0226916877
1580031019 0226944097
1580031811 0226959703
1580034732 0225977890
1580039715 0226960851
1580039715 0226960850
1580040911 0225950912
1580047213 0225950911
1580047213 0226929151
1580047213 0226970181
1580047213 0226970191
7380055017 0226916890

Sheet 2
A/c Number
1080019977
1080032707
1080043119
1080043670
1180039669
1180040600
1180040600
1180044784
1180047183
1180081623
1280049857
1280049857
1380037984
1380047736
1380047736
1380049757
1380070079
1480043536
1480047040
1480059467
1580018719
1580031019
1580031811
1580039715
1580039715
1580040911
1580040911
1580047213
1580047213
1580047213
1580047213
7380055017

thanks
straus

See my contrib in

http://mrexcel.com/board/viewtopic.php?topic=3146&forum=2

for a possible approach you can adapt to your retrieval problem.

 

[DonkeyOte]

So do you want the account number repeated twice on Sheet2 (in which case I really don't understand what you're trying to do) or do you want to list the multiple phone numbers against the account code...

i.e

Col A Account Number
Col B First Tel No
Col C Second Tel No
Col D Third Tel No

etc...