| Name | Modified | Size | Downloads / Week |
|---|---|---|---|
| PowerVlookup.xlam | 2017-07-28 | 90.7 kB | |
| Readme.txt | 2017-07-27 | 3.1 kB | |
| Totals: 2 Items | 93.8 kB | 4 |
This little program is born due to an excessive use of the famous Excel formula "vlookup" and the other less known combination "index/match". I hope that those who know either one of them will get the hang of it right away. Important: ********** -> PowerVLookup only works in Excel sheets with data beginning in the first cell. -> The first line must contain the columns headers. -> You need at least 2 sheets of data in order to use PowerVLookup efficiently. (This doesn't necessarily has to be in one workbook) -> It is good to know that the worksheet in wich the results will be stored is the ActiveSheet, the one currently selected. Installation: ************* -> How to install an Excel Add-in : https://support.office.com/en-us/article/View-manage-and-install-add-ins-in-Office-programs-16278816-1948-4028-91E5-76DCA5380F8D -> How to create a new button on the office ribbon: https://support.office.com/en-us/article/Assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c User Guide: *********** Main Window Title : PowerVLookup - Results will be stored in column XX (in the ActiveSheet) Index Workbook : Dropdownlist containing all open Excel workbooks, select the one containing the data you need Index Sheet : Dropdownlist containing all the sheets of the selected Index Workbook, again select the sheet containing the required data Match & Lookup Column : The dropdownlist will contain the identical headers (case sensitive) present in both sheets (active sheet & index sheet) In case the headers are spelled differently (ex. "CUST_NR" & "Cust_Nr" or "CUST_NR" & "CUSTOMER_NR") goto options panel and select the columns manually Index Column : Here you select the column that contains the data you want. Example : Workbook 1 Sheet 1 Sheet 2 A B C D A B C D E ---------------------------------------- ------------------------------------------------ 1 CL_NR CL_NM CL_ADR 1 INV_NR INV_P INV_Q CL_NR 2 CL001 John USA 2 I001 50 2 CL002 3 CL002 Sandra France 3 I002 45 2 CL001 4 CL003 Mike Canada 4 I003 10 1 CL002 5 CL004 Paul China 5 I004 150 3 CL004 For some reason i need to add the second column of sheet 1 "CL_NM" in sheet 2 in correspondance with the data present in both sheets, the column "CL_NR". Run PowerVlookup macro... In this case my main window will look as follows : Index Workbook = Workbook 1 Index Sheet = Sheet 1 Match & Lookup = CL_NR Index Column = CL_NM Once all the fields are filled click on the start button (2nd button) and watch column E getting filled. The result should look like this : Sheet 1 Sheet 2 A B C D A B C D E ---------------------------------------- ------------------------------------------------ 1 CL_NR CL_NM CL_ADR 1 INV_NR INV_P INV_Q CL_NR CL_NM 2 CL001 John USA 2 I001 50 2 CL002 Sandra 3 CL002 Sandra France 3 I002 45 2 CL001 John 4 CL003 Mike Canada 4 I003 10 1 CL002 Sandra 5 CL004 Paul China 5 I004 150 3 CL004 Paul
