You are here:

# Excel/vlookup, if, choose

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?

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
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Hi Tom - Thats works perfectly!!! Thank you so much! FYI when working with the German version you need to change the formula names, of course, but also "," need to be change ";", ":" stay the same. Since I am used to working with the English version, these always present another layer of diffiuclty for me. But I am learning :) Thanks again. Have a nice weekend.

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

#### 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.