Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vlookup to get Named Range

0

Good Morning all,

I have a workbook where I need to use a Vlookup to get pricing by work location and EE Name.  Basically, I'm trying to use a vlookup to lookup the named range (Ranges tab, rows 1-3) and then use that named range in another vlookup to get the pricing for that work location. I need the results to show up on the Data tab in column G. I tried the following to no avail:

=VLOOKUP(F2,"Ranges!" & VLOOKUP(Ranges!A1:B3,2,FALSE),2,FALSE)

The data is extracted from an access database and I need to format for billing statements to different EE's with different work locations.

For row 2 on the data tab, i would need it to return 20 (AB&C Co / Urban location)

For row 3 on the data tab, I would need it to return 15.

Any help would be appreciated.

Answer
Discuss

Answers

0
Selected Answer

You need to use the INDIRECT function to convert the looked-up range name to a range address. Please enter this formula in DATA!G2 and copy down.

=VLOOKUP($F2,INDIRECT(VLOOKUP($C2,Ranges!$A$2:$B$3,2,FALSE)),2,FALSE)

I would replace Ranges!$A$2:$B$3 with a named range "Ranges"

Discuss

Discussion

Hello amberella, How did you make out? Did you get your lookups to work as you wanted?
Variatus (rep: 4889) Jun 15, '19 at 8:00 pm
Yes, thank you so much for your help!
amberella23 (rep: 2) Jun 26, '19 at 1:25 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login