Lookup

S

Steve

I am trying to do the following and wondered if anyone knew if, and
how it could be done:


Worksheet A

Column A Column B Column C

A1 Jones, Steve B1 Muntral, Henry C1 1-1
A2 Collins, Mark B2 Fraser, Brian C2 1-2
A3 Godfrey, Brian B3 Jones, Harry C3
A4 Rogers, Geoff B4 Bullen, Patrick C4




Worksheet B

Column A Column B Column C Column D

A1 S Jones B1 H Muntral 1 1
A2 M Collins B2 B Fraser 1 2



What I would like to achieve is for Column C in worksheet A to be
populated with the results of Column C and Column D of Worksheet B but
only if Column A and Column B from Worksheet A are found in Column A
and Column B in worksheet B

So in the example above Column C1 in Worksheet A would be 1-1, C2 1-2,
C3 empty and C4 empty


In worksheet A columns A and B could contain up to 1000 entries while
columns A and B in worksheet B could contain 2/3 hundred

I hope this makes sense and thanks in advance

Steve
 
C

Claus Busch

Hi Steve,

Am Tue, 25 Sep 2012 17:12:00 +0100 schrieb Steve:
Worksheet A

Column A Column B Column C

A1 Jones, Steve B1 Muntral, Henry C1 1-1
A2 Collins, Mark B2 Fraser, Brian C2 1-2
A3 Godfrey, Brian B3 Jones, Harry C3
A4 Rogers, Geoff B4 Bullen, Patrick C4

Worksheet B

Column A Column B Column C Column D

A1 S Jones B1 H Muntral 1 1
A2 M Collins B2 B Fraser 1 2

What I would like to achieve is for Column C in worksheet A to be
populated with the results of Column C and Column D of Worksheet B but
only if Column A and Column B from Worksheet A are found in Column A
and Column B in worksheet B
Click to expand...

try in C1 of sheet A:
=LOOKUP(2,1/('Sheet B'!A1:A100&'Sheet B'!B1:B100=A1&B1),'Sheet B'!C:C)&"-"&LOOKUP(2,1/('Sheet B'!A1:A100&'Sheet B'!B1:B100=A1&B1),'Sheet B'!D:D)


Regards
Claus Busch
 
S

Steve

Hi Claus, nd thank you very much for your help but I am getting a #N/A
response

I am not sure but does the Lookup(2,1/ aspect of the forumula search
for text as the format of the name in Column A in worksheet A is
different from the format in Column B

If so, any other ideas as to why I am getting a #N/A response?

Thanks very much

Steve
 
C

Claus Busch

Hi Steve,

Am Wed, 26 Sep 2012 16:44:32 +0100 schrieb Steve:
I am not sure but does the Lookup(2,1/ aspect of the forumula search
for text as the format of the name in Column A in worksheet A is
different from the format in Column B

If so, any other ideas as to why I am getting a #N/A response?
Click to expand...

you are gettung #N/A if the names are not in sheet B or if names in
sheet A are written different then in sheet B


Regards
Claus Busch
 
C

Claus Busch

Hi Steve,

sorry, in sheet B the names are written different.
Try:
=LOOKUP(2,1/('Sheet B'!$A$1:$A$100&'Sheet B'!$B$1:$B$100=MID(A1,FIND(" ",A1)+1,1)&" "&LEFT(A1,FIND(",",A1)-1)&MID(B1,FIND(" ",B1)+1,1)&" "&LEFT(B1,FIND(",",B1)-1)),'Sheet B'!C:C)&"-"&LOOKUP(2,1/('Sheet B'!$A$1:$A$100&'Sheet B'!$B$1:$B$100=MID(A1,FIND(" ",A1)+1,1)&" "&LEFT(A1,FIND(",",A1)-1)&MID(B1,FIND(" ",B1)+1,1)&" "&LEFT(B1,FIND(",",B1)-1)),'Sheet B'!D:D)



Regards
Claus Busch
 
S

Steve

Hi Claus that works perfectly - thank you very much.

However, I think I am going mad because I cant seem to implement same
in my workbook !

The actual references for my workbook are as follows:

Sheet B A14:A400, B14:B400, H:H and I:I

Is it perhaps that H and I have values that are a result of a formula
as follows:
=COUNTIF(A$14:$A14,A14) for H14
=COUNTIF($B$14:B14,B14) for I14 ?

Sorry to have to ask again but it is a question of so near yet.....

Thanks in advance

Regards

Steve
 
C

Claus Busch

Hi Steve,

Am Thu, 27 Sep 2012 12:54:30 +0100 schrieb Steve:
The actual references for my workbook are as follows:

Sheet B A14:A400, B14:B400, H:H and I:I
Click to expand...

because your values start in row 14, you have to change
H:H to $H$14:$H$400 and the same for I:

=LOOKUP(2,1/('Sheet B'!$A$14:$A$400&'Sheet B'!$B$14:$B$400=MID(A14,FIND(" ",A14)+1,1)&" "&LEFT(A14,FIND(",",A14)-1)&MID(B14,FIND(" ",B14)+1,1)&" "&LEFT(B14,FIND(",",B14)-1)),'Sheet B'!$C$14:$C$400)&"-"&LOOKUP(2,1/('Sheet B'!$A$14:$A$400&'Sheet B'!$B$14:$B$400=MID(A14,FIND(" ",A14)+1,1)&" "&LEFT(A14,FIND(",",A14)-1)&MID(B14,FIND(" ",B14)+1,1)&" "&LEFT(B14,FIND(",",B14)-1)),'Sheet B'!$D$14:$D$400)


Regards
Claus Busch
 
S

Steve

Hi Claus and thanks for the quick reply.

This is how I have amended but still getting #N/A

I am sure one of the values I am putting in is wrong but cant figure
out which one !!!!

=LOOKUP(2,1/('Sheet B'!$A$14:$A$378&'Sheet
B'!$B$14:$B$378=MID(R148562,FIND(" ",R148562)+1,1)&"
"&LEFT(R148562,FIND(",",R148562)-1)&MID(S148562,FIND("
",S148562)+1,1)&" "&LEFT(S148562,FIND(",",S148562)-1)),'Sheet
B'!H14:H378)&"-"&LOOKUP(2,1/('Sheet B'!$A$14:$A$376&'Sheet
B'!$B$14:$B$376=MID(R148562,FIND(" ",R148562)+1,1)&"
"&LEFT(R148562,FIND(",",R148562)-1)&MID(S148562,FIND("
",S148562)+1,1)&" "&LEFT(S148562,FIND(",",S148562)-1)),'Sheet
B'!I14:I378)

Sorry but I think I have confused this further.

Where it says MID and LEFT I am referring back to the other worksheet
to test if the info is there, is that correct?

Thanks

Steve
 
C

Claus Busch

Hi Steve,

Am Thu, 27 Sep 2012 13:25:08 +0100 schrieb Steve:
=LOOKUP(2,1/('Sheet B'!$A$14:$A$378&'Sheet
B'!$B$14:$B$378=MID(R148562,FIND(" ",R148562)+1,1)&"
"&LEFT(R148562,FIND(",",R148562)-1)&MID(S148562,FIND("
",S148562)+1,1)&" "&LEFT(S148562,FIND(",",S148562)-1)),'Sheet
B'!H14:H378)&"-"&LOOKUP(2,1/('Sheet B'!$A$14:$A$376&'Sheet
B'!$B$14:$B$376=MID(R148562,FIND(" ",R148562)+1,1)&"
"&LEFT(R148562,FIND(",",R148562)-1)&MID(S148562,FIND("
",S148562)+1,1)&" "&LEFT(S148562,FIND(",",S148562)-1)),'Sheet
B'!I14:I378)
Click to expand...

'Sheet B'!H14:H378 must be absolute:
'Sheet B'!$H$14:$H$378
and the same for I14:I378


Regards
Claus Busch
 
S

Steve

Claus, thanks very much - I feel that I am going mad because I can see
it clearly works yet when I put it in my own worksheet I now get
nothing (the #N/A has gone!)

I have taken up too much of your time already so I will have a look
later on and reply when I have had a chance to think about it more.

Thank you very much again
Regards

steve
 
S

Steve

Hi Claus, I have checked the spreadsheet and thanks for doing that
because it really helps me understand how the formual works.

However, I still cannot get it to work and the only thing i can think
of is that the values in H and I are the results of another formula -
could that be possible?.

I will update your spreadsheet and re-upload and if you can get a
chance to look at it that would be appreciated.

Regards


steve



ls for doing that On Thu, 27 Sep 2012 15:13:16 +0200, Claus Busch
 
C

Claus Busch

Hi Steve,

Am Thu, 27 Sep 2012 16:39:58 +0100 schrieb Steve:
Hi Claus, I have checked the spreadsheet and thanks for doing that
because it really helps me understand how the formual works.

However, I still cannot get it to work and the only thing i can think
of is that the values in H and I are the results of another formula -
could that be possible?.
Click to expand...

I tested it with formulas and it is working.
I think you have unexpected spaces in your names


Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MATCH OR VLOOKUP 3
Help!!! Need formula for boss 1
***Help on writing a vba code 12
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Data Validation and drop-down list using IF, AND and what else? 8
Show other cell if blank 5
Figuring out a lengthy formula. Probably quite simple... 3
Excel lookup across entire workbook 1

Top