Picking data out of different columns

[tuph]

The purpose of the attached workbook is to calculate the average number of days a customer takes to pay their invoices. There are two tabs - Invoices and Payments. I am trying to calculate the number of days between Invoices "Sales Date" and Payments "Settlement Date".

The Payments tab is raw data exported from Attache financial system as CSV. It is the best I can get out of the system but the columns are all over the place.

I thought maybe from the Invoices tab I could search a row on the Payments tab to match the invoice number, find the cell containing the phrase "Invoice Number" then pick up the date from the following cell which contains the Settlement Date . This would be straight forward if the data from the CSV file consistently landed in the same column in the Payments worksheet, but it doesn't. As you can see, the phrase "Invoice Number" might appear in any one of several different columns.

I would be most grateful if anyone suggest a method for this lookup please.

[bebo021999]

Try in L8:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[protonLeah]

Maybe a UDF:

Please Login or Register  to view this content.

[wcymiss]

There is also a LOOKUP solution than does not require Ctrl+Shift+Enter:

Please Login or Register  to view this content.

[tuph]

Thanks everyone for your input.

wcymiss - I must be missing something but your formula returns the same data regardless of the invoice number in column A.

bebo - thanks for your formula - it works well but only returns data if the invoice number is in column D. Is it possible to make it look at the entire worksheet and return the data from columns E:R also?

Protonleah - thanks very much for your sample workbook. It looked amazing when I first opened it but when I try to save it or edit it the formulas all return a #VALUE error. I do not know anything about UDFs so I can't fix it myself. Are you able to suggest a solution to this problem please?

[tuph]

#VALUE problem solved itself!!! protonLeah your UDF works exactly as I need it to and I have managed to copy it into my real reporting workbook with just under 14,000 rows and it still works. Thank you soooo much.