software

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

gt; .. I have had a reply, but I
gt; typed in the following it did not seem to work.
gt; =INDEX(Delivery Notes!$F$1:$F$500,MATCH(1,(F6=Delivery
gt; 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 lt;ggt;),
remember to enter the array formula by pressing CTRL SHIFT ENTER
(instead of just pressing ENTER)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Freddoquot; wrote:
gt; I have two spreadsheets, I want to see if the same information is on both
gt; spreadsheets, but I have to compare two columns at the same time so I cant
gt; use VLOOK up in the convetionational way unless it can look up two cells. eg
gt; I have one spread sheet which we shall call sheet1 with information in
gt; Columns A,B,C,and D and I have another spreadsheet called sheet2 within
gt; information in coulmns A,B,C, and D. I want to know if a row in sheet1 has
gt; the same infomation in coulmn A and B as a same row in column A and B in
gt; sheet2. If it has please show column C in sheet1 in column E in sheet 2,
gt; please note the infomation must be in the same row.I have had a reply, but I
gt; typed in the following it did not seem to work.=INDEX(Delivery
gt; Notes!$F$1:$F$500,MATCH(1,(F6=Delivery
gt; Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell) When you
gt; click ctrl shift and enter the curly brackets start in front of the first =
gt; and close at the very end. This is the reply I got.
gt; =index(othersheet!$c$1:$c$100,
gt; match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
gt; (one cell)
gt;
gt; This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
gt; correctly, excel will wrap curly brackets {} around your formula. (don't type
gt; them yourself.)
gt;
gt; Adjust the range to match--but you can't use the whole column.
gt;
gt; This returns the value in othersheet column C when column A and B (of
gt; othersheet) match A2 and B2 of the sheet with the formula.
gt;
gt; And you can add more conditions by just adding more stuff to that product
gt; portion of the formula:
gt;
gt; =index(othersheet!$d$1:$d$100,
gt; match(1,(a2=othersheet!$a$1:$a$100)
gt; *(b2=othersheet!$b$1:$b$100)
gt; *(c2=othersheet!$c$1:$c$100),0))
gt;
gt; Please could somebody check it and tell what I am doing wrong

gt; (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,..)

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---Thanks for your help I have tried to make the range the same but it stills
come up with #NAME?

quot;Maxquot; wrote:

gt; gt; (Or, perhaps it should be amended the other way round ..)
gt;
gt; The above means perhaps change the 2 ranges within the MATCH part to:
gt; Delivery Notes!$C$1:$C$500
gt; Delivery Notes!$E$1:$E$500
gt; (to be consistent with .. INDEX(Delivery Notes!$F$1:$F$500,..)
gt;
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;

quot;Freddoquot; wrote:
gt; Thanks for your help I have tried to make the range the same
gt; 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 quot;invisiblequot; white spaces in either which
could throw the matching off)
*except for case

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

=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

quot;Maxquot; wrote:

gt; quot;Freddoquot; wrote:
gt; gt; Thanks for your help I have tried to make the range the same
gt; gt; but it stills come up with #NAME?
gt;
gt; Could you copy the *actual* formula (copy it from the formula bar)
gt; and paste in your response here ?
gt;
gt; Check also that the sheetname: Delivery Notes
gt; in the formula is correctly spelled and match exactly* what's on the tab
gt; (look out for any extra quot;invisiblequot; white spaces in either which
gt; could throw the matching off)
gt; *except for case
gt;
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---

quot;Freddoquot; wrote:
gt; =INDEX(Delivery Notes!$F$2:$F$500,MATCH(1,(F6=Delivery
gt; 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 befo
=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.

gt; This is the formula in my formula bar
gt; 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.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

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

quot;Maxquot; wrote:

gt; quot;Freddoquot; wrote:
gt; gt; =INDEX(Delivery Notes!$F$2:$F$500,MATCH(1,(F6=Delivery
gt; gt; Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell)
gt;
gt; Try this corrected formula (lightly tested here ok),
gt; array-entered as befo
gt; =INDEX('Delivery Notes'!F2:$F$500,MATCH(1,(F6='Delivery
gt; Notes'!$C$2:$C$500)*(O6='Delivery Notes'!$E$2:$E$500),0))
gt;
gt; Think the culprit was the 2 missing apostrophes: ' just before and after the
gt; sheetname ref in the formula. It's good practice to include these
gt; apostrophes, irregardless whether there are spaces in the sheetname or not.
gt;
gt; gt; This is the formula in my formula bar
gt; gt; the curly brackets dont seem to have been pasted
gt;
gt; Yes, that's ok. The curly braces are inserted by Excel (not to be typed in)
gt; and won't be copied.
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---

quot;Freddoquot; wrote:
gt; Thanks that seems to work
gt; it was the words one cell I needed to get rid of

Glad to hear that worked out ok lt;bggt; !

Dave meant it: the quot;(one cell)quot; bit, as an explanation
that the entire formula should be placed all in one cell
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

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 wrote:
gt;
gt; quot;Freddoquot; wrote:
gt; gt; Thanks that seems to work
gt; gt; it was the words one cell I needed to get rid of
gt;
gt; Glad to hear that worked out ok lt;bggt; !
gt;
gt; Dave meant it: the quot;(one cell)quot; bit, as an explanation
gt; that the entire formula should be placed all in one cell
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---

--

Dave Peterson