VLOOKUP using a dynamic range

[Guest]

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

 

[Guest]

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'!D220,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)