tenacityit
asked on
Need a vlookup / index excel formula
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
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
ASKER
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
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
ASKER
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
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
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
The attached working example only has two sheets and so the script has been edited to allow for this
Book1.xlsm
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
The attached working example only has two sheets and so the script has been edited to allow for this
Book1.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
You just add names in any order .....it will return to max value of that name in sheets between Start to Finish.
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
Thanks
ASKER
Great solution, worked perfectly
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
Open in new window
If I have missed the point could you please post an example workbook with some dummy data