Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP using a dynamic range
Hi, I need a way to set the start and end of the range to be used in my
vlookup formula based on a value. So I need to set the range that the vlookup formula uses to be dynamic based on the Resource last name. Here is my current formula: =IF(ISNA(VLOOKUP($A2,'Resource Data'!$B$5:$D$9,3,FALSE)),0,VLOOKUP($A2,'Resource Data'!$B$5:$D$9,3,FALSE)) In this case I am targeting the rows where "Miller" is the Resource. But instead of me having to change the range to look up, I would like a way that the start and end of the range is set based on the the resource name. I tried named ranges but I do not know what the refers to would be to dynamically set the range based on whatever resource value I need. Resource Data Table Column A Column B Column C Column D Resource Project ID Proj Name 2007 Hours 1 Smith 1234567 Project X 56 2 Smith 3334546 Project A 134 3 Smith 4456768 Project P 445 4 Smith 3345778 Project V 4567 5 Miller 2234454 Project y 89 6 Miller 5564788 Project B 123 7 Miller 6675899 Project H 455 8 Miller 6674849 Project F 80 9 Miller 9989979 Project T 223 10 Jones 2229986 Project G 124 11 Jones 4450090 Project J 2324 12 Jones 6675889 Project D 78 13 Jones 6678888 Project W 99 14 Jones 9990004 Project M 778 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a dynamic named range in a VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup - dynamic range reference? | Excel Discussion (Misc queries) | |||
Dynamic range for Table_array in a VLOOKUP. | Excel Worksheet Functions | |||
Urgent Dynamic Range with Vlookup | Excel Discussion (Misc queries) | |||
Dynamic Range for Function (Vlookup etc) | Excel Worksheet Functions |