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?
This seems fairly straightforward.
Put this formula in cell D55 and copy down:
=IF(ISERROR(VLOOKUP(A55,WI
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
Put this formula in cell D55 and copy down:
=IF(ISERROR(VLOOKUP(A55,WI
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
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,
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,Em
See attached with updated solution.
Enjoy!
Dave
Wells-Fargo-Hours-Summary-4.30.1.xlsx
=WIP!$C$3:INDEX(WIP!$J:$J,
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,Em
See attached with updated solution.
Enjoy!
Dave
Wells-Fargo-Hours-Summary-4.30.1.xlsx
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
Here's the final update with that simplification on the Employee_Hours named range...
Enjoy!
Dave
Wells-Fargo-Hours-Summary-4.30.1.xlsx
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was great, thank you for helping me to understand.
ASKER