You are here:

Excel/vlookup, if, choose

Advertisement


Question
Hi Tom - I am creating an invoice template which will be used by several people. I have created the document to autofill most of the condition such as pricing per contract, customer name etc, mostly by using vlookup and if formulas.

I have now hit a wall trying to autopopulate the section for billing period. We need a date range such as billing period 01.12.13 to 31.12.13. I would like this to be populated when the users fills in the invoice date which is no earlier than the first of the following month. So when they enter invoice Date 01.01.14 the previous date range is populated.

I was able to create a matrix of date and using the "=WENN(UND(F1>=B8;F1<=C8);D7;WENN(UND(F1>=B9;F1<=C9);D8;WENN(UND(F1>=B10;F1<=C10);D9;WENN(UND(F1>=B11;F1<=C11);D10;WENN(UND(F1>=B12;F1<=C12);D11;WENN(UND(F1>=B13;F1>=C13);D12;WENN(UND(F1>=B14;F1<=C14);D13;WENN(UND(F1>=B15;F1<=C15);D14;WENN(UND(F1>=B16;F1<=C16);D15;WENN(UND(F1>=B17;F1<=C17);D16;WENN(UND(F1>=B18;F1<=C18);D17;0)))))))))))"

This falls over after the 7th if statement and I know there is a much better way. please note that this formula is is German and WENN=IF and UND=AND :)

Any ideas?

Answer
Chris,

You could do a match on the start date column (column B) with a 1 as the third argument meaning the lookup range is sorted ascending. this should return the location from the highest date that is less than or equal to the date being looked up.  This should be the row you want.  


=Match(F1,$B$8:$B$30,1)

so
=Index($D$7:$D$29,match(F1,$B$8:$B$30,1),1)

should give you the results you want.  Just make sure your table will support all the invoice dates that you will use.

Note that I wrote the formula to reflect your last start date as being in row 30 since you didn't say.  Just change the formula to reflect the actual table range.  Also, I noted in your formula that if the invoice fell within the range for B8 to C8, you returned the value from D7; one row prior.  So that is why I index on a range that starts and ends one row before your date range start and end. Hopefully what I have written is clear and you can implement it.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.