# Array look up

#### chrisrick

##### New Member
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?

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### barry houdini

##### MrExcel MVP
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

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
I've removed the ) and it worked fine.

Chris

Replies
1
Views
325
Replies
1
Views
348
Replies
4
Views
179
Replies
3
Views
190
Replies
1
Views
86

1,191,118
Messages
5,984,749
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back