OK Excell wizards how do I do this

  • Thread starter Thread starter patcha
  • Start date Start date
P

patcha

I started to get some help but it didnt work so
Let me try and explain this better.

On some worksheet say 3, I have a certain cell say B20 that is lookin
to match the value C20 and D20 from columns N and Q from worksheet 2
when it finds this match it will concatentate from that same row th
text values in A and C in worksheet 2. now the cell below B20 so B2
will do the same thing only it will look for a match of values in C2
and D21 from the same columns (N and Q) from worksheet 2. and if i
finds the match it will also concatenate the corrisponding vales fro
that row from columns A and C in worksheet 2.

does this make sense what I am trying to do
 
Is this what you mean?

=IF(AND(Sheet3!B20=Sheet2!C20,Sheet3!B20=Sheet2!D20),Sheet2!A20&Sheet2!B20&S
heet2!C20,"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If I understand correctly, this might help:

=OFFSET(Testing2!$A$1,MATCH($C20,Testing2!N:N,1)-1,0) &
OFFSET(Testing2!$C$1,MATCH($D20,Testing2!Q:Q,1)-1,0)
 
OK maybe you have already given me what I need but just to make sure.

in worksheet2 I have a cells B20 C20 and D20. now say in C20 I have
the value 10 and in D20 I have entered the value 110. What I want B20
to do is go to worksheet1 and look in column N from the range of
N1:N200 for the value in C20(10) and try to find the match in Column Q
from the same range Q1:200 for the value in D20(110). Now when those
two rows match, so say N188 = 10 and Q188 = 110 I want it to
concatenate from that same worksheet the text that is in A188 with the
text in C188. Does this help.
 
Patcha,

I think i've got it

Requirements (to make sure i've understood)
Sheet1 C20 matches Sheet2 Column N
Sheet1 D20 matches Sheet2 Column Q
Return values of Column A & Column C from the row where the match occurs
ie if C20 matches N31 AND D20 matches Q31 then return A31 & C31.

=INDEX(Sheet2!A1:A50,IF(SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*
(ROW(Sheet2!N1:N50))),SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*
(ROW(Sheet2!N1:N50))),1)) & INDEX(Sheet2!C1:C50,IF(SUMPRODUCT((Sheet2!N1:N50=C20)*
(Sheet2!Q1:Q50=D20)*(ROW(Sheet2!N1:N50))),SUMPRODUCT((Sheet2!N1:N50=C20)*
(Sheet2!Q1:Q50=D20)*(ROW(Sheet2!N1:N50))),1))

Dan E
 
Oh,

I set it up so that if no match occurs the contents of A1 & C1 (or your
first and second rows) will be returned. So you might want to put
"No Match" in the first row so you know if it happens...

Dan E

Dan E said:
Patcha,

I think i've got it

Requirements (to make sure i've understood)
Sheet1 C20 matches Sheet2 Column N
Sheet1 D20 matches Sheet2 Column Q
Return values of Column A & Column C from the row where the match occurs
ie if C20 matches N31 AND D20 matches Q31 then return A31 & C31.

=INDEX(Sheet2!A1:A50,IF(SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*
(ROW(Sheet2!N1:N50))),SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*
(ROW(Sheet2!N1:N50))),1)) & INDEX(Sheet2!C1:C50,IF(SUMPRODUCT((Sheet2!N1:N50=C20)*
(Sheet2!Q1:Q50=D20)*(ROW(Sheet2!N1:N50))),SUMPRODUCT((Sheet2!N1:N50=C20)*
(Sheet2!Q1:Q50=D20)*(ROW(Sheet2!N1:N50))),1))

Dan E
Click to expand...
 
this has all been vary helpfull. is there a simpler way to put this
into excell terms.

if(range1=criteria1 and range2=criteria2 then concantinate in the
corisponding range3 and range4)
 
Patcha,

This is a little simpler and returns "No match" on an error

=IF(SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*(ROW(Sheet2!N1:N50)))<>0,
INDEX(Sheet2!A1:A50,SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*
(ROW(Sheet2!N1:N50)))) & INDEX(Sheet2!C1:C50,SUMPRODUCT((Sheet2!N1:N50=C20)*
(Sheet2!Q1:Q50=D20)*(ROW(Sheet2!N1:N50)))),"No Match")

SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*(ROW(Sheet2!N1:N50)))
This statement returns the rownumber of the match, if no match is found a zero is returned

INDEX(Sheet2!A1:A50,SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*
(ROW(Sheet2!N1:N50))))
returns the row A match

INDEX(Sheet2!C1:C50,SUMPRODUCT((Sheet2!N1:N50=C20)*(Sheet2!Q1:Q50=D20)*
(ROW(Sheet2!N1:N50))))
returns the row C match

IF(There is a match, return row A & row C (ie concatenation), "No match")

Dan E
 
Back
Top