Lookup Subject to Conditions

[Graemea]

Hi,


In A2:A1000 in Sheet1 I have series of consecutive month-end dates (31/07/2012, 31/08/2012 etc).


In A2:A700 in Sheet2 I have a series of consecutive month-end dates and in B2:B700 the associated monthly sales values.


In A2:A700 in Sheet3 I have a series of consecutive month-end dates and in B2:B700 the associated monthly sales values. There is some overlap
in the dates between Sheet2 and Sheet3.


In B2 in Sheet1 I would like to create a formula that will return the sales value for the date in A2, based on a lookup that considers the data
in Sheets2 and 3:


1. If the date A2 is in A2:A700 in Sheet2 and the associated value in B2:B700 is not a blank cell nor a zero, return the value from B2:B700
in Sheet2.


2. If these conditions aren't met, switch the lookup to Sheet3 and return the value from B2:B700; if the date in A2 isn't in A2:A700 in Sheet3
the return "".


Can someone please suggest a formula that will achieve this?


Hope that this makes sense!


Thanks!

 

[DanteAmor]

How about.
Put this formula in B2 on Sheet1 and copy down.

=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),IFERROR(VLOOKUP(A2,Sheet3!A:B,2,0),""))

 

[joshman108]

It's a little long but this will do what you requested and also will not fail if a date is present on sheet 1 but not on sheet 2. In that case it assumes it WILL be on sheet 3. This also assumes you only ever have one instance of each date and are only looking to return a single value, not sum values if you had multiple of the same dates.

=IF(COUNTIFS(Sheet2!A:A,A2)>0,IF(NOT(OR(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0),1)=0,INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0),1)="")),INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0),1),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0),1)),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0),1))

 

[Graemea]

Thanks very much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.