?
Solved

INDEX / VLOOKUP Excel Help

Posted on 2011-05-11
7
Medium Priority
?
226 Views
Last Modified: 2012-05-11
I have a multi-tab worksheet.  Am trying to lookup a value in a specific column (rows change periodically as data is added), for which the returned value matches a user name the primary tab.  For example, I would like to look up 'Hours', in Tab: WIP, Column J, and return the value to cell 'WIP Data', in Tab: Hours Summary, Column D, for which the user name in both tabs matches.  Can anyone please help?
0
Comment
Question by:davidkohne
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35742884
This seems fairly straightforward.

Put this formula in cell D55 and copy down:

=IF(ISERROR(VLOOKUP(A55,WIP!$C$3:$G$53,5,0)),0,VLOOKUP(A55,WIP!$C$3:$G$53,5,0))

the first part ensures there's a matching name - otherwise zero is reported, and the vlookup looks at:

Vlookup(lookup_value - e.g., NAME reference, Array - e.g., the table of the data you're referencing where the first column is the same key as the lookup_value, 0 - e.g., looks for an EXACT match)

See attached.

Enjoy!

Dave
Wells-Fargo-Hours-Summary-4.30.1.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35742945
I created this dynamic named range Employee_Hours, to generate the address for the Array - given the rows may change in the WIP tab.  This works as long as the data starts in row 3 and the label for the hours table in column A includes the word "Total" - though you'll see you can change the named range should you change that label:

=WIP!$C$3:INDEX(WIP!$J:$J,MATCH("*Total*",WIP!$A:$A,0)-1)
So, the formula starts with $C$3 - just like the previous vlookup array.  The rest of this finds the bottom-most row in column J.


As a result, the Vlookup formula for cell D55 copied down is:

=IF(ISERROR(VLOOKUP(A55,Employee_Hours,5,0)),0,VLOOKUP(A55,Employee_Hours,5,0))
See attached with updated solution.

Enjoy!

Dave
Wells-Fargo-Hours-Summary-4.30.1.xlsx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dlmille
ID: 35743213
A final word - I think the dynamic range is a bit overkill.  As long as you insert INSIDE the range from row 3 to 53, you should be fine with a simple defiend name...

Here's the final update with that simplification on the Employee_Hours named range...


Enjoy!

Dave
Wells-Fargo-Hours-Summary-4.30.1.xlsx
0
 

Author Comment

by:davidkohne
ID: 35743456
This seems to be ideal, however, I believe the statements are pulling from the wrong WIP column.  The numbers shown on the summary tab, should be from column J of the WIP tab.  Tried to modify this in your example, but I dont fully understand the logic.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35743468
Just change the lookup to 8 columns over from the key column...

=IF(ISERROR(VLOOKUP(A55,Employee_Hours,8,0)),0,VLOOKUP(A55,Employee_Hours,8,0))

Here it is:

Dave
Wells-Fargo-Hours-Summary-4.30.1.xlsx
0
 

Author Closing Comment

by:davidkohne
ID: 35743491
This was great, thank you for helping me to understand.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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