Solved

Need a vlookup / index excel formula

Posted on 2015-01-18
9
82 Views
Last Modified: 2015-01-21
Hi All

I have an excel workbook with 17 Sheets.

On each sheet Column "A" list of names, Column "B" has a value that varies.

On some days there are more names in Column "A" than other days.

In the results Worksheet, I need to lookup the value in cell "A1" across all worksheets Column "A", then return the Maximum Value in Column "B" for that entry.

Any assistance would be greatly appreciated!

Thanks
David
0
Comment
Question by:tenacityit
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 40556818
I not sure I am getting your question

So the Max value for cell B1 across sheets 1 to 17 or will there multiple instances of the name in column A which need to be accounted for

The following example covers 5 sheets and just needs to be expanded if it is just the MAX of column B across the sheets

=MAX(Sheet1!B1,Sheet2!B1,Sheet3!B1,Sheet4!B1,Sheet5!B1)

Open in new window


If I have missed the point could you please post an example workbook with some dummy data
0
 

Author Comment

by:tenacityit
ID: 40556835
Thanks for your reply.

The name in Column A could appear multiple times or not at all across the worksheets.

It needs to lookup the name from the master list of names located in Column A on the results worksheet.

I hope that helps.

Thanks
David
0
 

Author Comment

by:tenacityit
ID: 40556949
Worksheet 1

Column A       Column B
john                 113
Paul                 121
Sarah               225
Shaun             126
Greg                99
Mary                897

Worksheet 2

Column A       Column B
Greg                 222
Michael            121
Sarah               565
Shaun             836
john                 199
Mary                347



Results Worksheet

Column A      Column B
John              (Result)
Michael      (Result)
Sarah           (Result)
Greg            (Result)
Shaun          (Result)
Mary            (Result)
Paul             (Result)

As you can see in Worksheet 1 there is no user Michael and Worksheet 2 there is no user Paul.

What I need is to search for Name based on the list in the Results Worksheet, and then return the Max Value in Column B to the results worksheet.

Does that clarify?

Thanks
David
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40556975
Here is a VBA script that performs this for you. It assumes sheets named Sheet1 to Sheet17 for the data and one named Results. This can be tweaked as required

Sub results()
    Dim resultsSht As Worksheet, currSht As Worksheet
    Dim resultsLastRow As Long, currShtLastRow As Long, shtNum As Long, currMax As Long, i As Long
    Dim loc As Range, firstLoc As Range
    
    Set resultsSht = Worksheets("Results")
    resultsLastRow = resultsSht.Range("A" & Rows.Count).End(xlUp).Row
    
    
    For i = 1 To resultsLastRow
        currMax = 0
        For shtNum = 1 To 17
            Set currSht = Worksheets("Sheet" & shtNum)
            currShtLastRow = currSht.Range("A" & Rows.Count).End(xlUp).Row
            With currSht.UsedRange
                Set loc = .Cells.Find(What:=resultsSht.Range("A" & i).Value)
                If Not loc Is Nothing Then
                    Set firstLoc = loc
                    Do
                        currMax = loc.Offset(0, 1).Value
                        Set loc = .FindNext(loc)
                    Loop While Not loc Is Nothing And loc <> firstLoc
                End If
            End With
            Set loc = Nothing
        Next
        resultsSht.Range("B" & i).Value = currMax
    Next
    
End Sub

Open in new window


The attached working example only has two sheets and so the script has been edited to allow for this
Book1.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Accepted Solution

by:
itjockey earned 500 total points
ID: 40557089
May be this will help you ...see attached .....you have to add sheets in which you want to find lookup or max or sum between sheet "Start - Finish".

See attached.

Thanks
3D-Functions.xlsm
0
 

Author Comment

by:tenacityit
ID: 40558631
Thanks for the replies.

itjockey, You're on the right track, however the Name won't be in the same cell on each worksheet. It needs to lookup the name first in Column A. and you can't sort the column alphabetically as there will be more names on some sheets than others.

Thanks
David
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40558664
You just add names in any order .....it will return to max value of that name in sheets between Start to Finish.
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40558690
Why you want to sort or lookup names ...it is excel formula which search for particular name on each sheets in column A between Start - Finish sheets and returns to max value of that particular name across the sheet.Beauty of this set up is you add sheet between Start - Finish and you will see change in main sheet.

Thanks
0
 

Author Closing Comment

by:tenacityit
ID: 40563598
Great solution, worked perfectly
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

911 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