Array look up

[chrisrick]

I am trying to create an invoice statement which contains multiple entries. I would like this to be automated as much as possible.

My source data is coming from one sheet which contains client name, date description of work and costs.

I have used the Array lookup formula's described in the MS help files titled "How to look up a value in a list and return multiple corresponding values" successfully before to return multiple values. However the task above requires that the values returned are dependant on Client and dates between the 1st and the last day of a specified month. The formula I am using dosn't return any values:

{=SMALL(IF(AND($A$2:$A$20=$L$5,$B$2:$B$20>$A$18,$B$2:$B$20<=$A$19),ROW($A$2:$A$20)),ROW(1:1))}

Source Data:
A2:A20 = Client name
B2:B20 = Date

Criteria:
A18 = Start Date
A19 = End Date
L5 = Client name

According to the documentation in the MS help files this should return the line number of each item that matches the criteria of which there are several - I get "0".

Can any one help?

 

[barry houdini]

Hello Chris,

You can't use AND function in these sorts of formulas, also if clients are in A2:A20 then the start and end dates can't be in A18 and A19, unless these are on a different sheet, so assuming start is in A21 and end in A22 you could try this variation, confirmed with CTRL+SHIFT+ENTER

=SMALL(IF($A$2:$A$20=$L$5,IF($B$2:$B$20>$A$21,IF($B$2:$B$20<=$A$22,ROW($A$2:$A$20)))),ROW(1:1))

You could incorporate this into an INDEX function to return a specific value from the row in question rather than just the row number

 

[Krishnakumar]

Hi,

=SMALL(IF($A$2:$A$20=$L$5,IF($B$2:$B$20>$A$18,IF($B$2:$B$20<=$A$19),ROW($A$2:$A$20)))),ROW(1:1))

Array entered.

 

[chrisrick]

Thanks for the information.

I've tried entering the revised formula supplied by Barry (you are correct in that A18 & A19 were in fact on a different sheet - i've moved the data to the suggested cells A21 & A22.

When I try and enter the formula using Ctrl + Shift + Enter I receive a message saying that the formula contains an error and the $A$22 is highlighted.

Any idea what could be wrong?

 

[barry houdini]

Hello Chris,

I believe the formula I posted is OK. Kris' formula has a superfluous ) after $A$19 so if you copied that you'll get an error, just remove the parenthesis and it should work...

 

[chrisrick]

I've removed the ) and it worked fine.

Thankyou both for your help.

Chris