Dynamic range reference in VLOOKUP?

Is there a way to create a dynamic range reference in VLOOKUP?

I am creating a table of MS hot fixes for our subnet, class C. The first table is a list of the IP addresses in column A and each additional column will be the status of the hotfix listed as the column header. Each additional table contains the imported results from a MBSA scan on each PC with the sheet title set to the IP address.

I can create a static reference that works, "=VLOOKUP(C2,'161.211.103.2'!$B:$C,2,FALSE)", but I REALLY don't want to have to create static references for 100+ worksheets.

I have tried substituting the range reference by using the Concatenate() function to build the range reference from the A column key, but I get a #Value error. The same Concatenate() function in its own cell does return the desired range reference.

If not, perhaps someone has a better idea on how to accomplish this lookup. The range reference is the same for each sheet, its just that each row item refers to a different sheet.

Thanks for any ideas,

Dave

Originally Posted by drhawk

Is there a way to create a dynamic range reference in VLOOKUP?

I am creating a table of MS hot fixes for our subnet, class C. The first table is a list of the IP addresses in column A and each additional column will be the status of the hotfix listed as the column header. Each additional table contains the imported results from a MBSA scan on each PC with the sheet title set to the IP address.

I can create a static reference that works, "=VLOOKUP(C2,'161.211.103.2'!$B:$C,2,FALSE)", but I REALLY don't want to have to create static references for 100+ worksheets.

I have tried substituting the range reference by using the Concatenate() function to build the range reference from the A column key, but I get a #Value error. The same Concatenate() function in its own cell does return the desired range reference.

If not, perhaps someone has a better idea on how to accomplish this lookup. The range reference is the same for each sheet, its just that each row item refers to a different sheet.

Thanks for any ideas,

Dave

you should try INDIRECT function.

Thanks.

While it didn't work within the VLOOKUP(), I was able to build the range reference in another cell using CONCATENATE(), then use INDIRECT() within VLOOKUP() to reference the cell with the range text.

Ex:
Range text =CONCATENATE("'",A3,"'!$B:$C")
Lookup data =VLOOKUP(D2,INDIRECT(B3),2,FALSE)


Dave

Originally Posted by drhawk

Thanks.

While it didn't work within the VLOOKUP(), I was able to build the range reference in another cell using CONCATENATE(), then use INDIRECT() within VLOOKUP() to reference the cell with the range text.

Ex:
Range text =CONCATENATE("'",A3,"'!$B:$C")
Lookup data =VLOOKUP(D2,INDIRECT(B3),2,FALSE)


Dave

you can use it within VLOOKUP into INDIRECT by using & to join the values. Infact & will do the same as CONCATENATE does.

Regards