Array formula

QuestionEdit

QUESTION: Somebody helped me before with this question but I forgot the formula. I have two separate databases on excel. What I want to do is to come up with a formula that will relate to columns in the first database with two more columns from the other database, and if those values match, return a value from the second the database. For example

DB1>

COLUMN 1 COLUMN 2 COLUMN3 8/5/2008 11:30 78 *FORMULA* 8/5/2008 11:30 79 *FORMULA*

DB2

COLUMN 1 COLUMN 2 COLUMN3 8/5/2008 11:30 78 23.5 8/5/2008 11:30 79 24.7

I remember that the formula that somebody gave me started with =MIN(IF(...) but I don't remember how the rest went. I also remember that it took a lot of time to run the formula and fill all the values. I gave those two large databases and was wondering if there is a faster way to run this formula o get the values that I want.

ANSWER: Assume you have 2 sheets, db1 and db2, with the data starting in cell A1 of each sheet.

In cell D1 of db2, enter

=A1&B1

and copy down col D.

In cell C1 of db1, enter

=INDEX('db2'!C:C,MATCH(A1&B1,'db2'!D:D,0),1)

and copy down col C.

---------- FOLLOW-UP ----------

QUESTION: I am not sure if I understand your answer. Let me explain again what I am looking. In Column 3/C of DB1, I want to find out the formula that I need to relate Column 1/A and Column 2/B from DB1 to Column 1/A and Column 2/B of DB2. If those same values match then in Column 3/C of DB1 I want the exact value from Column 3/C of DB2. I am not sure if that's what you were trying to do with your formula or if I simply did not understand how to do it. Thanks a lot,

Mario

ANSWER: As an alternate solution, you could enter in cell C1 of db1

=MAX(IF('db2'!A$1:A$100=A1,IF('db2'!B$1:B$100=B1,'db2'!C$1:C$100,0),0))

as an array, then copy down the col.

---------- FOLLOW-UP ----------

QUESTION: Thanks a lot for your help! I have another question. What is the difference between using =MIN(IF(... and =MAX(IF(... function? Also, one of the problems I am having is that my 2 databases have over 100,000 rows of data. So when I used this formula it is really slow and oftencrashes after a while. I am not sure if there is a faster, more efficient way of getting those same results. Thanks again for your help,

Mario

AnswerEdit

The meaning of the max(if( formula I provoded is: for each row of db2, if both col a and b match the db1 values, call it the value in col c; otherwise, call it value of zero; then take the maximum of all those values, for your result in col c of db1..

If you want a simple, faster method, refer to the first answer I gave you... Concatenate cols a and b of db2 into col d, then use the index(match( formula I provided to bring the col c result from db2 to db1.

QuestionEdit

I have an array formula for tracking when calibrations are due. I want the cell to have "Due"and stay "Due" if it is 30 days or less before the due date. If it exceeds the due date, it will have "Overdue". Everything works fine, except I want the cell to stay "Due" until it is overdue. I can only make the "Due" appear on the one day that is exactly 30 days and then it goes to overdue the next day. Here is the formula that I am using. =IF(I15="","",IF($A$1<I15,"OK",IF($A$1=I15,"DUE","OVERDUE")))

AnswerEdit

First, this isn't an array formula -- regular entry is fine. Second, I see no test for being 30 days or less before the due date, so it'd help to know what's in A1 and I15. If A1 has today and I15 has due date, you can try this:

=IF(I15="","",IF(I15-A1>30,"OK",IF(I15-A1>0,"DUE","OVERDUE")))=IF(I15="","",IF(I15-A1>30,"OK",IF(I15-A1>0,"DUE","OVERDUE")))