Ross wrote:
Hello,

I have a table of data that has dates, production lines and production
volumes (table one):

Production Line Date Volume
A02 13/3/06 189
C32 13/3/06 325
T12 14/3/06 21


The table contains data retrieved from the beginning of the year so
there is a substantial amount, I have on a separate sheet a list of
further data (table two):

Production Line Date Tank
AD02 13/3/06 A
CD32 13/3/06 C
TD12 14/3/06 B


I want to use a formula that references two cells ( Production line
and date in the first table) and returns from the second table the
tank that the particular line was flowing into on that date.
You will also notice an additional problem between the two table is
that table one Production Line reads "A02" and table two's
Production Line reads "AD02" so obviously this formula will have to
ignore the additional D.

Someone has suggested an array formula but seeing as I am using this
formula possible several thousand times on one sheet it is slowing
the whole workbook down far too much.

Thanks for any help in advance.

P


Hi Ross,

the only way without using VBA, AFAIK, is an array formula like this:

=INDEX(Sheet2!C2:C4,,MATCH(A2,LEFT(Sheet2!A2:A4,1) &RIGHT(Sheet2!A2:A4,2),0)*MATCH(B2,Sheet2!B2:B4,0) )

Type (or copy and paste) the formula in cell D2 on your first sheet,
assuming that the sheet with tank information is "Sheet2", press
Ctrl+Shift+Enter, then copy down the formula.

You could also try with VBA, but in this case I could not help you, because
VBA it's not my strong point...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy