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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP using a dynamic range
where A1=Miller and A2 is the Project ID you are looking for, try:
=IF(ISNUMBER(MATCH(1,('Resource Data'!A2:A20=A1)*('Resource Data'!B2:B20=A2),0)),INDEX('Resource Data'!D2:D20,MATCH(1,('Resource Data'!A2:A20=A1)*('Resource Data'!B2:B20=A2),0)),"") entered with Cntrl+Shift+Enter. If done properly, excel will put braces { } around the formula. alternatively, you could insert a column in your table and concatenate the resource and project ID =B2&" "&C2 to create a unique lookup key. Then use your vlookup on this new field =VLOOKUP($A1&" "&$A2,'Resource Data'!$A$2:$E$9,5,FALSE) "CSK" wrote: 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 |
Reply |
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 |