Lookup table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We invoice customers in weekly batches (Table A). Recurring invoices
are due to be raised at various intervals eg: monthly, 28 days, 18 days etc
(Table B). What formual can I put in Table A that shows the right
invoice date from Table B in the right column on Table A

TABLE A
Invoicing batches Batch 1 Batch 2 Batch 3
Invoice date - From 27/07/05 03/08/05 10/08/05
Invoice date - To 02/08/05 09/08/05 16/08/05
Customer 1 ??? ??? ???
Customer 2 ??? ??? ???
Customer 3 ??? ??? ???

TABLE B
Invoice dates
Customer 1 Mth 01/08/05 01/09/05 01/10/05
Customer 2 28 02/08/05 30/08/05 27/09/05
Customer 3 18 25/07/05 12/08/05 30/08/05

With thanks
 
I'm not sure if this is what you want, but here's a guess...

Assumptions:

A1:E6 contains Table A, including column/row labels (as per your example)

A10:E13 contains Table B, including column/row labels (as per your
example)

Formula:

C4, copied down and across:

=INDEX($C$11:$E$13,MATCH($A4,$A$11:$A$13,0),MATCH(1,(INDEX($C$11:$E$13,MA
TCH($A4,$A$11:$A$13,0),0)>=C$2)*(INDEX($C$11:$E$13,MATCH($A4,$A$11:$A$13,
0),0)<=C$3),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Back
Top