Comparing two spreadsheets part 2

[Freddo]

I have two spreadsheets, I want to see if the same information is on both
spreadsheets, but I have to compare two columns at the same time so I cant
use VLOOK up in the convetionational way unless it can look up two cells. eg
I have one spread sheet which we shall call sheet1 with information in
Columns A,B,C,and D and I have another spreadsheet called sheet2 within
information in coulmns A,B,C, and D. I want to know if a row in sheet1 has
the same infomation in coulmn A and B as a same row in column A and B in
sheet2. If it has please show column C in sheet1 in column E in sheet 2,
please note the infomation must be in the same row.I have had a reply, but I
typed in the following it did not seem to work.=INDEX(Delivery
Notes!$F$1:$F$500,MATCH(1,(F6=Delivery
Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell) When you
click ctrl shift and enter the curly brackets start in front of the first =
and close at the very end. This is the reply I got.
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

Please could somebody check it and tell what I am doing wrong

 

[Max]

.. I have had a reply, but I

typed in the following it did not seem to work.
=INDEX(Delivery Notes!$F$1:$F$500,MATCH(1,(F6=Delivery
Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))

Some thoughts as to what's wrong with your implementation ..

This part: INDEX(Delivery Notes!$F$1:$F$500
should be amended to: INDEX(Delivery Notes!$F$2:$F$500
to be consistent in range size to those in cols C and E
(Or, perhaps it should be amended the other way round ..)

And as per the steps given by the responder (Dave P, I presume <g>),
remember to enter the array formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)

 

[Max]

(Or, perhaps it should be amended the other way round ..)

The above means perhaps change the 2 ranges within the MATCH part to:
Delivery Notes!$C$1:$C$500
Delivery Notes!$E$1:$E$500
(to be consistent with .. INDEX(Delivery Notes!$F$1:$F$500,..)

 

[Freddo]

Thanks for your help I have tried to make the range the same but it stills
come up with #NAME?

 

[Max]

Thanks for your help I have tried to make the range the same
but it stills come up with #NAME?

Could you copy the *actual* formula (copy it from the formula bar)
and paste in your response here ?

Check also that the sheetname: Delivery Notes
in the formula is correctly spelled and match exactly* what's on the tab
(look out for any extra "invisible" white spaces in either which
could throw the matching off)
*except for case

 

[Freddo]

=INDEX(Delivery Notes!$F$2:$F$500,MATCH(1,(F6=Delivery
Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell)

This is the formula in my formula bar the curly brackets dont seem to have
been pasted

 

[Max]

=INDEX(Delivery Notes!$F$2:$F$500,MATCH(1,(F6=Delivery
Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell)

Try this corrected formula (lightly tested here ok),
array-entered as before:
=INDEX('Delivery Notes'!F2:$F$500,MATCH(1,(F6='Delivery
Notes'!$C$2:$C$500)*(O6='Delivery Notes'!$E$2:$E$500),0))

Think the culprit was the 2 missing apostrophes: ' just before and after the
sheetname ref in the formula. It's good practice to include these
apostrophes, irregardless whether there are spaces in the sheetname or not.

This is the formula in my formula bar
the curly brackets dont seem to have been pasted

Yes, that's ok. The curly braces are inserted by Excel (not to be typed in)
and won't be copied.

 

[Freddo]

Thanks that seems to work it was the words one cell I needed to get rid of

 

[Max]

Thanks that seems to work
it was the words one cell I needed to get rid of

Glad to hear that worked out ok <bg> !

Dave meant it: the "(one cell)" bit, as an explanation
that the entire formula should be placed all in one cell

 

[Dave Peterson]

Thanks for the correction/amplification.

And one trick to getting those pesky apostrophes to work correctly is to change
the sheet name to something that doesn't require them -- maybe just A (single
character A).

Then get the formula working and then rename the sheet.

If excel wants the apostrophes, it'll add them.

 

[Max]

Thanks for the tip, Dave !