INDEX / VLOOKUP Excel Help

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?
davidkohneAsked:
Who is Participating?
 
dlmilleCommented:
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
 
dlmilleCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
davidkohneAuthor Commented:
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
 
davidkohneAuthor Commented:
This was great, thank you for helping me to understand.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.