Lookup Formula Help - PLEASE!

[VickyC]

Good Morning,

I am having huge problems trying to find the formula I need.

I have a large spreadsheet and another document which is the Bill. What I need to do is to look up Column M on the Spreadsheet between cells 10 to 500. and if there is a figure in any cells to return column A.

if there is more than one figure between that range then I want the second to appear in the cell below in the bill and so on.

Hope this makes sense, If anyone can help I will be so grateful.

Kind Regards, Vicky

[Dav]

can u provide more detail

Is the data in column M unique? and is it numeric?

Is it the data in column A unique

Regards

Dav

[VickyC]

Hi Dav

Column M are Cost Amounts, Column A is the Ref: Number which is unique and Column B is the name of the customer.

In a seperate document I have a bill template.
Column A called Ref:
Column B called Details
Column C called Costs

I need to fill this information in from the Spreadsheet, so I need a formula to look up Column M in the Spreadsheet and if there is an amount in there then enter the amount into Column C of the Template but I then will need to do a simular Formula to relate Column A and B to column A and B in the Spreadsheet?

Thanks,

[Dav]

=IF(SUMPRODUCT(LARGE(($M$10:$M$500>0)*(ROW($M$10:$M$500)),ROW(A3)-2))>0,OFFSET($A$1,SUMPRODUCT(LARGE(($M$10:$M$500>0)*(ROW($M$10:$M$500)),ROW(A3)-2))-1,0)," ")



Not an elegant solution and it returns the invoice lines in the reverse order.
I have assumed that the formula will start in the 3rd row of the spreadsheet hence A3
To return, the values from column M use the amendment below
Each range starting $M will need to be superceded by your sheet name of the invoice, but I do not know what you are calling it!


=IF(SUMPRODUCT(LARGE(($M$10:$M$500>0)*(ROW($M$10:$M$500)),ROW(A3)-))>0,OFFSET($M$1,SUMPRODUCT(LARGE(($M$10:$M$500>0)*(ROW($M$10:$M$500)),ROW(A3)-2))-1,0)," ")

It would be nice to have used a similar formula with the small function, but you would need the smallest excluding 0 as that is what is returned when the criteria are nor met, this would insure the order on the invoice would be the same as ir appears on the other sheet. I am sure someone will know how to do it.

At least it is a start!

Regards

Dav

[Dav]

If the following is entered as an Array Ctrl Shift Enter, it will return the invoice in the correct order! Again make the changes as in the previous post
=OFFSET($A$1,SMALL(IF(($M$10:$M$500>0)*($M$10:$M$500),(ROW($M$10:$M$500))),ROW(A3)-2)-1,0)

However when a match can not be made it will return #num! On the sheet, to overcome this conditionally format the sheet to make the cells with this format to go white!
So if the formula is in cell A3 enter in the conditional formating =ISERR(a3) and set the point colour to white!

Regards

Dav