Matching Cells

[idrinkwater]

Hello
Hope all is going well
Im soooo glad that I have found this site its awesome and full of great stuff
Well Ive been trying to figure this one out for a long time but had no luck
I have sheet 1 displays the results sheet 2 has the data
In sheet 1 im trying to get a cell to give me the result of a matching postcode and price if the cells in sheet 2 dont match then the result will go to the new box

Thanks in Advance

 

[Aladin Akyurek]

Somewhat unclear...

Given a reg and a postcode, say in A20 and in B20...

C20, Sheet1, control+shift+enter, not just enter:

=INDEX(Sheet2!$C$2:$C$7,MATCH(1,IF(Sheet2!$A$2:$A$7=A20,IF($B$2:$B$7=B20,1)),0))

would yield the associated price.

Given a reg and a price, say in A21 and B21...

C21, Sheet1, control+shift+enter:

=INDEX(Sheet2!$B$2:$B$7,MATCH(1,IF(Sheet2!$A$2:$A$7=A21,IF($C$2:$C$7=B21,1)),0))

would yield the associated postcode.

 

[idrinkwater]

Thanks for your post

Sorry about it being confusing but it was really late

I didnt get a chance to play around with the function you posted but its close to what im looking for

the result i am trying to get is the Reg with the same poscode and same price which then should be filled in to cells f6,g6,b7,c7,d7,

 

[Aladin Akyurek]

Thanks for your post

Sorry about it being confusing but it was really late

I didnt get a chance to play around with the function you posted but its close to what im looking for

the result i am trying to get is the Reg with the same poscode and same price which then should be filled in to cells f6,g6,b7,c7,d7,

A20: br (a postcode of interest)

A21: 40 (a price of interest)

A22, on Excel 2007 and later:

=COUNTIFS($B$2:$B$7,A20,Sheet2!$C$2:$C$7,A21)

Otherwise:

=SUMPRODUCT(--($B$2:$B$7=A20),--(Sheet2!$C$2:$C$7=A21))

A24, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$24:A24)<=$A$22,INDEX($A$2:$A$7,
    SMALL(IF($B$2:$B$7=$A$20,IF(Sheet2!$C$2:$C$7=$A$21,
      ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($A$24:A24))),"")

 

[idrinkwater]

=INDEX(Sheet2!$A:A,MATCH(1,IF(Sheet2!$B:B=VLOOKUP(E6,Sheet2!A:C,2,FALSE),IF(Sheet2!$C:C=VLOOKUP(E6,Sheet2!A:C,3,FALSE),1)),0))

 

[idrinkwater]

what i am trying to do is match the postcode with the price to get the reg if they dont match then the next reg should go down in to E11
it should look like this
f6=cccc
e11=dddd
b14=20
the next new box should have
e17=eeee
f16=wwwww
b19=50

 

[Aladin Akyurek]

what i am trying to do is match the postcode with the price to get the reg if they dont match then the next reg should go down in to E11
it should look like this
f6=cccc
e11=dddd
b14=20
the next new box should have
e17=eeee
f16=wwwww
b19=50

Try the set up in my last post in order to see whether it gives you the desired results. That set up yields the reg result given a postcode and price.