NEW! Free Questions Regarding Newsletter ONLY. You need a username and password from the Excel Help forum. If you do not have one, join here for free.
Rather than bogging down your Workbook with hundreds or thousands of lookup formulas, use one to lookup multiple tables and values. The download below is kind of like a formula approach to PivotTables
Download the Working Sample
Free Questions Regarding Newsletter ONLY. You need a username and password from the Excel Help forum. If you do not have one, join here for free.
SEE ALSO: VLOOKUP | Vlookup Across Worksheets | 5 Condition Vlookup | 4 Condition Vlookup | 3 Condition Vlookup | 2 Condition Vlookup | How to stop the #N/A! error | Lookup Any Occurrence in Any Table Column || Hlookup Formula || Left Lookup in Excel || Excel Lookup Functions | Multi-Table Lookup
of DownloaderXL and AnalyzerXl
of Real Time Quotes Excel
of Bulk Quotes Excel
of Portfolio Excel
Fill Handle & Custom Lists
Get up to 80% OFF Software & make money $$
Here are 2 little known tips to get up to 80% off (including our products at 30% off) thousands of software titles.
The 1st one is via Regnow, who are leaders in selling software online. Simply sign up here and then start browsing or search (YOU MUST HAVE A USERNAME & PASSWORD BY SIGNING UP HERE. If you do not have a Website, simply sign with Google for a FREE ONE.
The second is to to get 30% (already at half price) of Excel Dashboard Kit, by signing up here for free.
Excel VBA Macro Codes
Try this Custom Function, which should work in Excel 2000 or above.
Used in any cell like;
Function Lookup_Occurence(To_find, Table_array As Range, _ Look_in_col As Long, Offset_col, Occurrence As Long, _ Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean) Dim lLoop As Long Dim rFound As Range Dim xlLook As XlLookAt Dim lOcCheck As Long If Part_cell_match = False Then xlLook = xlWhole Else xlLook = xlPart End If Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1) On Error Resume Next lOcCheck = WorksheetFunction.CountIf _ (Table_array.Columns(Look_in_col), To_find) If lOcCheck < Occurrence Then Lookup_Occurence = vbNullString Else For lLoop = 1 To Occurrence Set rFound = Table_array.Columns(Look_in_col).Find _ (What:=To_find, After:=rFound, LookAt:=xlLook, LookIn:=xlValues, _ MatchCase:=Case_sensitive) Next lLoop On Error GoTo 0 Lookup_Occurence = rFound.Offset(0, Offset_col) End If End Function
Used in any cell like;
Jan is the value to find. (To_find)
$A$1:$C$5000 is data table AND A1:C1 has headings. (Table_array)
2 is the column (relative to Table_array) of the data table to look in, column B in this case. (Look_in_column)
-1 is the offset from the found occurrence (Column A in this case). It can be positive or negative. (Offset_column)
3 is the occurrence of Jan to find in Column B. (Occurrence)
Or, if you use TRUE, TRUE (the default if omitted is FALSE, FALSE) for the last 2 optional arguments (Case_sensitive and Part_cell_match) it becomes case sensitive and looks for part cell matches. That is, a cell housing Jan. Is the next month. or The next month is Jan. or Next month is Jan, followed by Feb, will all be considered matches.
If the number of occurrences (based on your arguments) is not possible, the Function returns empty text.
Hundreds more free Excel add-ins, templates, tutorials & demos.
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft