Comparing two spreadsheets

F

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.
 
D

Dave Peterson

=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))
 
F

Freddo

=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) This is the
formula I put in but it does not seem to work please re-check, please advise
where the curly brackets go they come up around everything, ie in front of
the first equals and at the very end.
 

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

Top