Solved

Hlookup or Index(match))

Posted on 2012-03-23
19
1,018 Views
Last Modified: 2012-08-13
=INDEX(Sheet8:Sheet1300!$A$2:$E$3,,MATCH(B9,Sheet8:Sheet1300!$A$1:$E$1,0))
=HLOOKUP(B9,Sheet7:Sheet1300!$B$1:$F$6,2,0)
I have 1300 sheets and one sheet with phone numbers in the B column on sheet7
In B1of all the sheets i have a phone number
and B2 i have the name
I want to return the name for all phone numbers on sheet 7.
I have used both formulas above and they work but only when referencing one sheet.
0
Comment
Question by:Jenedge73
  • 9
  • 5
  • 4
  • +1
19 Comments
 
LVL 3

Expert Comment

by:DaFranker
ID: 37757850
Since "Sheet7" and "Sheet1300" are just names for the sheets, you can't tell Excel to go "from sheet7 to sheet1300", because what happens if you move Sheet1300 before Sheet7?

The only way I know of to do something like this - looping through sheets - is to use VBA. You might have to use a UDF (User Defined Function) for this.
0
 

Author Comment

by:Jenedge73
ID: 37757935
This is a one shot deal.  It's actually the company cell phone bill that I converted from PDF.  The summary sheet of the bill only has the phone numbers and no names.  I'm trying to cross the phone number on the summary page with the corresponding name.  Since I’m only doing this once the sheet sequence will always remain the same.  If the sheet sequence remains the same 1300 will never be before 7 will a function work.  The reason i ask is I'm terrible at VBA
0
 
LVL 80

Expert Comment

by:byundt
ID: 37759715
Here is a user-defined function that will find the first worksheet in which HLOOKUP does not return an error. It will then use that worksheet with the regular HLOOKUP function and return the desired value.

Install the code in a regular module sheet. Use the fx icon to the left of the formula bar, then pick "User defined functions" from the resulting dropdown. You may then pick the HLookup3D function and enter the parameters in the function wizard. The resulting formula should look like:
=HLookup3D(B9,Sheet7:Sheet1300!$B$1:$F$6,2,False)

Function HLookup3D(LookupVal As Variant, LookupTable, nRow As Long, bExactMatch As Variant) As Variant
'Function works just like HLOOKUP function, except can handle 3-D cell range references
'LookupTable may be single sheet or multi-sheet references to a range of cells _
    Enter 3-D ranges just like in a SUM formula:   =HLookup3D("Profits",'Sheet 1:Sheet 3'!A1:H7, 2, False)
'LookupVal may be text or number
'Function returns the value from row nRow on first worksheet where there is a match for LookupVal
'Note: there must be only one HLookup3D function in a formula--the wrong answer will be returned if there is more than one.
Dim cel As Range
Dim iFirstCheck As Integer, iLastCheck As Integer, k As Integer
Dim vResults As Variant, vTable As Variant
Dim wbTable As Workbook
On Error Resume Next
Set cel = Application.Caller
If cel Is Nothing Then
    HLookup3D = "#NoRange"
    Exit Function
End If

vTable = Parse3D(cel.Cells(1), "HLookup3D", 2)
Set wbTable = Workbooks(vTable(0))
iFirstCheck = wbTable.Worksheets(vTable(1)).Index
iLastCheck = wbTable.Worksheets(vTable(2)).Index

If VarType(LookupTable) = 10 Then
    For k = iFirstCheck To iLastCheck
        vResults = Application.HLookup(LookupVal, wbTable.Worksheets(k).Range(vTable(3)), nRow, bExactMatch)
        If Not IsError(vResults) Then Exit For
    Next
Else
    vResults = Application.HLookup(LookupVal, LookupTable, nRow, bExactMatch)
End If

HLookup3D = vResults
End Function

Private Function Parse3D(FormulaCell As Range, fnName As String, parmIndex As Integer) As Variant
'Parses a formula looking for a specified function. If found, returns a variant array containing four strings: _
    workbook name, first worksheet name, last worksheet name and range address
'Function tolerates commas in workbook or sheet names, array constants and range unions
Dim i As Integer, i1 As Integer, i2 As Integer, i3 As Integer, j As Integer, k As Integer, n As Integer
Dim firstSheet As String, frmla As String, lastSheet As String, sPlaceHolder As String, sRange As String, _
    sSeparator As String, sSheets As String, sWorkbook As String, s1 As String, s2 As String
Dim nm As Name
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
sSeparator = ","        'The .Formula property uses a comma as list separator, no matter what the regional setting
sPlaceHolder = "?"      'This can be any character not found in workbook or worksheet names
frmla = FormulaCell.Formula
j = InStr(1, UCase(frmla), UCase(fnName) & "(")
If j > 0 Then
    sSheets = Mid(frmla, j + Len(fnName) + 1)
    
        'Eliminate any list separators that might be embedded in the formula
    For i = 0 To 4
        s1 = Array("'", """", "(", "{", "[")(i)
        s2 = Array("'", """", ")", "}", "]")(i)
        i1 = InStr(1, sSheets, s1)
        Do Until i1 = 0
            i2 = InStr(i1 + 1, sSheets, s2)
            If i <= 1 And Mid(sSheets, i2, 2) = (s1 & s1) Then i2 = InStr(i2 + 2, sSheets, s1)
            If i2 > 0 Then
                i3 = InStr(i1, sSheets, sSeparator)
                Select Case i3
                Case 0
                Case Is < i2
                    sSheets = Left(sSheets, i1) & Replace(Mid(sSheets, i1 + 1, i2 - i1 - 1), sSeparator, sPlaceHolder) & Mid(sSheets, i2)
                End Select
                i1 = InStr(i2 + 1, sSheets, s1)
            End If
        Loop
    Next
        
    sSheets = Split(sSheets, sSeparator)(parmIndex - 1)
    sSheets = Replace(sSheets, sPlaceHolder, sSeparator)    'Restore any list separators that had temporarily been replaced with splaceholder
    If Right(sSheets, 1) = ")" Then sSheets = Left(sSheets, Len(sSheets) - 1)
        
        'Test whether parameter is a named range
    On Error Resume Next
    Set nm = FormulaCell.Parent.Names(sSheets)
    If nm Is Nothing Then Set nm = FormulaCell.Parent.Parent.Names(sSheets)
    On Error GoTo 0
    If Not nm Is Nothing Then sSheets = Mid(nm.RefersTo, 2)     'Delete the initial = sign
    
    sSheets = Replace(sSheets, "''", "'")   'Single quotes embedded within sheet names are doubled up to escape them
    k = InStrRev(sSheets, "!")
    If k = 0 Then
        sRange = sSheets
        firstSheet = FormulaCell.Worksheet.Name
        lastSheet = FormulaCell.Worksheet.Name
    Else
        sRange = Mid(sSheets, k + 1)
        sSheets = Left(sSheets, k - 1)
        k = InStr(1, sSheets, "]")
        If k > 0 Then
            sWorkbook = Split(sSheets, "]")(0)
            If Left(sWorkbook, 1) = "'" Then sWorkbook = Mid(sWorkbook, 2)
            If Left(sWorkbook, 1) = "[" Then sWorkbook = Mid(sWorkbook, 2)
            sSheets = Split(sSheets, "]")(1)
        End If
        If Left(sSheets, 1) = "'" Then sSheets = Mid(sSheets, 2)
        If Right(sSheets, 1) = "'" Then sSheets = Left(sSheets, Len(sSheets) - 1)
        k = InStr(1, sSheets, ":")
        If k = 0 Then
            firstSheet = sSheets
            lastSheet = sSheets
        Else
            firstSheet = Split(sSheets, ":")(0)
            lastSheet = Split(sSheets, ":")(1)
        End If
    End If
    If sWorkbook = "" Then sWorkbook = FormulaCell.Worksheet.Parent.Name
    Parse3D = Array(sWorkbook, firstSheet, lastSheet, sRange)
End If
End Function

Open in new window


Brad
HLookup3D-Q27646166.xlsm
0
 
LVL 80

Expert Comment

by:byundt
ID: 37759771
For DaFranker's benefit, 3-D range references such as Jenedge73 showed in the original question have long been allowed in certain Excel functions like SUM. The functions that support 3D ranges as arguments are listed in Excel 2003 Help--look for "Refer to the same cell or range on multiple sheets". I don't know where the equivalent article is indexed in Excel 2010 Help. Unfortunately, 3-D range references are not permitted in lookup or conditional functions like COUNTIF, SUMIF, HLOOKUP or VLOOKUP.

I extended the capability of these last four functions so they can handle a 3-D range. Doing so requires the use of VBA code in a user-defined function. Unlike similar-purpose code you may find elsewhere, my user-defined functions accept a real 3-D range. They will update automatically whenever anything changes within that range in any of the worksheets. They will not update if worksheets or cells outside that range are changed.

Brad
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37760222
I think the VBA approach flexible and therefore superior, and I have a task in my mind to create a DLL using similar functionality that Brad has input to some time back.

However, there are formulaic approaches, just for the record, though I start to get a bit cross-sighted, here's one example...

Actually, I believe you can use these conditional functions - or simulate them, anyway.  Here's one example using COUNTIF to count the occurrences of the value "5" in A2:E3 of sheets 7:1300 (well, actually SUMPRODUCT to simulate COUNTIF):

Of course, this depends on the sheets actually existing, as opposed to having a starting/ending sheet as your VBA code would entertain, but it can be done.

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW($7:$1300)&"'!$A2:$E3"),"5"))


INDEX/MATCH/etc. MAY also be conducted in a like manner.

Dave
0
 
LVL 80

Expert Comment

by:byundt
ID: 37760680
Dave,
The return value is supposed to be a name, so you need HLOOKUP, INDEX & MATCH, etc.

For example:
=HLOOKUP(B9,INDIRECT("Sheet" & SUMPRODUCT(ROW($7:$1300)*COUNTIF(INDIRECT("Sheet" & ROW($7:$1300) & "!B1:F1"),B9)) & "!B1:F2"),2,FALSE)

Major limitation: the formula will fail if there is more than one occurrence of the phone number in Sheet7:Sheet1300!B1:F1

I've revised my sample workbook to include a variation on the above formula to prove that the approach works.

Brad
HLookup3D-Q27646166.xlsm
0
 
LVL 80

Expert Comment

by:byundt
ID: 37760767
I can avoid the major limitation mentioned in my last comment by using MAX in an array-entered formula. Now the only glitch occurs if the phone number is mentioned more than once on the same worksheet.
=HLOOKUP(B9,INDIRECT("Sheet" & MAX(ROW($7:$1300)*COUNTIF(INDIRECT("Sheet" & ROW($7:$1300) & "!B1:F1"),B9)) & "!B1:F2"),2,FALSE)

To array-enter a formula:
1) Select the cell and click in the formula bar
2) Hold the Control and Shift keys down
3) Hit the Enter key
4) Release all three keys. Excel should respond by putting curly braces { } surrounding your formula.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37761475
I knew if anyone could make that approach work, you could.  About all I've gotten it to do up to this point is count, lol.

You and barry are the best.

Dave
0
 
LVL 80

Expert Comment

by:byundt
ID: 37761519
Array-entered formula that eliminates even the restriction on not having duplicate phone numbers on the same worksheet:
=HLOOKUP(B9,INDIRECT("Sheet" & MAX(ROW($7:$1300)*(COUNTIF(INDIRECT("Sheet" & ROW($7:$1300) & "!B1:F1"),B9)>0)) & "!B1:F2"),2,FALSE)

Note that HLookup3D returns the first name corresponding to the first worksheet with a matching phone number, while the array formula above returns the first name corresponding to the last worksheet containing that phone number.

And as Dave mentioned, the formula will blow up if even one worksheet in the series Sheet7 through Sheet1300 is missing.

Brad
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Expert Comment

by:dlmille
ID: 37761537
Brad - I tried last night to create the array of sheets in another cell, and could not get the INDIRECT to work (returning the array) as it did when ROW($7:$1300) was embedded in the formula.  If that's possible, then there could be a bit more flexibility in the formulaic approach.
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 37761631
Dave,
You perhaps should have tried:
=HLOOKUP(B9,INDIRECT("'" & INDEX(SheetList,MAX(ROW(INDIRECT("1:" & ROWS(SheetList)))*(COUNTIF(INDIRECT("'" & INDEX(SheetList,,) & "'!B1:F1"),B9)>0))) & "'!B1:F2"),2,FALSE)

Array-entered, of course. SheetList is a named range containing the names of the worksheets to be searched, i.e. Sheet7 through Sheet1300. There may be no blanks within this range, and it is assumed to be arranged as a single column.

This is the first time in my life that I have used INDIRECT three times in a single formula, and I am definitely holding my nose due to the feculence of that formula.

Brad
HLookup3D-Q27646166.xlsm
0
 

Author Closing Comment

by:Jenedge73
ID: 37766750
WOW! Thanks man
0
 
LVL 80

Expert Comment

by:byundt
ID: 37766961
Jenedge73,
I'd be interested to know how long it takes for the INDIRECT formula to work in your real workbook with all the phone numbers compared to the HLookup3D function. Could you please post back in this same question with the results?

You can trigger the recalculation by selecting the column of cells with the formula, then replacing all the = sign with another = sign.

Thanks!

Brad
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37767001
I have great interest in that response as well.

Thanks for posting, Jenedge73!

Dave
0
 

Author Comment

by:Jenedge73
ID: 37768373
It's actualy pretty quick less than two minutes
0
 
LVL 80

Expert Comment

by:byundt
ID: 37768452
Jenedge73,
INDIRECT is a volatile function. Any formula containing INDIRECT will recalculate whenever any change is made to an open workbook. So if it takes two minutes to calculate the first time, it should take another two minutes when you type your name in a blank cell. The only way of avoiding this recalc time is to replace all the INDIRECT formulas with the values they returned (Edit...Copy followed by Edit...Paste Special...Values).

The user-defined function, on the other hand, is non-volatile. It will only recalculate when one of its arguments changes value. Furthermore, it quits searching once it finds an answer. So I think it might to be faster than the INDIRECT formula.

Both Dave and I are curious about the speed comparison because we'd like to offer good advice in the future regarding this type of problem. Especially if one way is clearly faster than the other.

Brad
0
 

Author Comment

by:Jenedge73
ID: 37768512
The Hlookup3d is tons more efficient.  It does calculate quicker and doesn’t keep recalculating.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37768525
Good to know

Thanks
0
 
LVL 80

Expert Comment

by:byundt
ID: 37768686
Jenedge73,
Thanks for being such a good sport and performing the test both ways.

While it might not make any real difference in a one-off question such as you were trying to solve, the next person may need to be making a number of adjustments to the workbook after getting the "answer."  In such cases, Dave and I now know that we should be pushing the HLookup3D approach.

The name of the website is Experts Exchange. It's in questions like this one where there was a good trade of information between all participants that the site lives up to its name.

Brad
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now