#1   Report Post  
Peter
 
Posts: n/a
Default Lookup table?

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
--
Peter
London, UK
  #2   Report Post  
Domenic
 
Posts: n/a
Default

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!

In article ,
"Peter" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create a lookup table with an added varable? GIZZMO Excel Worksheet Functions 3 April 6th 09 10:32 PM
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
Multiple table lookup KG Excel Discussion (Misc queries) 1 June 3rd 05 05:39 AM
How do I lookup a table from right to left ? Daz9 Excel Worksheet Functions 1 April 11th 05 11:21 AM
Index table lookup anomaly Carole O Excel Worksheet Functions 9 December 9th 04 05:33 PM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"