Tired of making the same reports every day ?
Want your job get done effectively and efficiently ?
.... Contact us immediately at affordsol49@gmail.com
"The definition of insanity is doing the same thing over and over and expecting different results." - (Benjamin Franklin)
|
Tired of making the same reports every day ?
|
|
Want your job get done effectively and efficiently ?
|
|
.... Contact us immediately at affordsol49@gmail.com
|
INDEX/MATCH
This is an excerpt from Pierre Leclerc (www.excel-vba.com)
| With INDEX/MATCH you can automate your invoices, quotation workbooks, business models.
You can also develop reports with data coming from many different sources. With these formulas you can virtually create relational databases in Excel and develop reports downstream.
They are more powerful and versatile than LOOKUP formulas. The quantities sold are in one data set, the name and the address of the client is in another set of data and then the product description is in a third set of data...no problem, a single INDEX/MATCH formula copied all the way down your column solves this problem. You now have a data set including sales, products and clients. Stop entering data manually in your workbooks and reports, use the easy to master INDEX/MATCH formula. The INDEX/MATCH formula is a formula using 2 functions : INDEX and MATCH. With the example below, the formula =INDEX(A2:E5,1,2) would return "Tiger Auto" because the formula translated in plain English says, what is the value found in the range "A2:E5" in the first row and the second column. The formula =MATCH(B11,A2:A5,0) in cell B12 would return 2 because the value in B11 (86598) is on the second row of the range A2:A5. The zero at the end of the formula tells Excel that you want an exact match. If you were looking for a number, a 1 instead of a zero would tell Excel to use the next higher value as a -1 would mean use the next lower value. With MATCH you don't have to sort the range or use FALSE or TRUE. Now let us replace the row number in the INDEX formula by the MATCH formula: =INDEX(A2:E5,MATCH(B11,A2:A5,0),2) and whenever you change the value in cell B11, a new value appears in cell B12. Here are some formulas combining INDEX and MATCH functions.
Tired of typing names, addresses, cities and states on invoices?
Each time you enter a Client #, the name, address, city and State are automatically modified. Even better, if you also have a database for your products you can enter the product number, and the description and unit price appear automatically avoiding even costlier mistakes. |
site by Affordable Solutions
Back Home