SOLVED

How to make a cell to reference a range name in vlookup formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1418354%22%20slang%3D%22en-US%22%3EHow%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418354%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20a%20vlookup%20formula%20that%20uses%20a%20cell%20reference%20that%20contains%20the%20name%20of%20a%20cell%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CarlMatDi_0-1590511854943.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194469iFE0F9A29623495AC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22CarlMatDi_0-1590511854943.png%22%20alt%3D%22CarlMatDi_0-1590511854943.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECell%20A3%20contains%20the%20name%20of%20a%20cell%20range%20%22MOVIL%22.%26nbsp%3B%20In%20cell%20D3%20I%20entered%20a%20formula%20that%20looks%20for%20a%20value%2C%20but%20that%20value%20depends%20of%20the%20range%20named%20in%20A3%20(there%20are%203%20other%20range%20names).%26nbsp%3B%20The%20Vlookup%20formula%20in%20D3%20is%3A%20%3DIFNA(IFERROR(ROUND(%24C3*VLOOKUP(%24B3%2C%22A3%22%2C4%2CFALSE)%2C2)%2CVLOOKUP(%24B3%2C%22A3%22%2C4%2CFALSE))%2C%22%22).%26nbsp%3B%20The%20problem%20is%20that%20vlookup%20does%20not%20understand%20that%20A3%20contains%20the%20name%20of%20a%20range.%26nbsp%3B%20I%20tried%20also%20without%20%22%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20fix%20this%20formula%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1418354%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418447%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418447%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F579635%22%20target%3D%22_blank%22%3E%40CarlMatDi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%2C%22A3%22%2C%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eyou%20need%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%2CINDIRECT(A3)%2C%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418456%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418456%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F579635%22%20target%3D%22_blank%22%3E%40CarlMatDi%3C%2FA%3E%26nbsp%3B%2C%20This%20should%20work%2C%20using%20INDIRECT%20function%20as%20shown%20%3A-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22amit_bhola_0-1590514580877.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194487i3AAF68EBE60FBD82%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22amit_bhola_0-1590514580877.png%22%20alt%3D%22amit_bhola_0-1590514580877.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418492%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20and%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20when%20I%20changed%20using%20INDIRECT%20a%20%23REF!%20error%20resulted%3A%3C%2FP%3E%3CP%3EI'm%20including%20the%20screenshot%20of%20the%20Name%20Manager.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CarlMatDi_0-1590515112581.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194494i330154BB418FCE61%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22CarlMatDi_0-1590515112581.png%22%20alt%3D%22CarlMatDi_0-1590515112581.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418504%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418504%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20but%20when%20I%20use%20INDIRECT%20a%20receive%20a%20%23REF!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CarlMatDi_0-1590515240398.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194498i437632FFCC1BCC6E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22CarlMatDi_0-1590515240398.png%22%20alt%3D%22CarlMatDi_0-1590515240398.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418519%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418519%22%20slang%3D%22en-US%22%3E%3CP%3EA%20detail%20that%20I%20didn't%20mentioned%20before%20is%20that%20A3%20value%20was%20filled%20using%20a%20drop%20down%20list%20that%20I%20defined%20using%20the%20data%20validation%20window.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418542%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418542%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F579635%22%20target%3D%22_blank%22%3E%40CarlMatDi%3C%2FA%3E%26nbsp%3B%2C%20your%20named%20range%20is%20referring%20to%20only%20one%20column%20A3%20to%20A500%20(%3D%26gt%3Bexcel%20knows%20about%20only%20one%20column)%2C%20but%20you%20are%20mentioning%204%20in%20the%20Vlookup%20formula%20%2C%20so%20excel%20cannot%20recognize%20the%204th%20column%2C%20that%20is%20why%20it%20is%20returni%20ng%20%23REF%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Vlookup%20to%20return%20the%20value%20on%204th%20column%20starting%20from%20column%20A%2C%20your%20named%20range%20should%20be%20something%20like%20%3DMovil!%24A%243%3A%24%3CFONT%20color%3D%22%23FF0000%22%3ED%3C%2FFONT%3E%24500%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1418588%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20to%20reference%20a%20range%20name%20in%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1418588%22%20slang%3D%22en-US%22%3EThank%20you!!%20That%20was%20the%20issue!%3CBR%20%2F%3EI%20didn't%20notice.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I'm trying to create a vlookup formula that uses a cell reference that contains the name of a cell range.

 

CarlMatDi_0-1590511854943.png

Cell A3 contains the name of a cell range "MOVIL".  In cell D3 I entered a formula that looks for a value, but that value depends of the range named in A3 (there are 3 other range names).  The Vlookup formula in D3 is: =IFNA(IFERROR(ROUND($C3*VLOOKUP($B3,"A3",4,FALSE),2),VLOOKUP($B3,"A3",4,FALSE)),"").  The problem is that vlookup does not understand that A3 contains the name of a range.  I tried also without "".

 

Can you help me fix this formula??

 

 

7 Replies
Highlighted

@CarlMatDi 

Instead of 

,"A3",

you need

,INDIRECT(A3),

Highlighted

@CarlMatDi , This should work, using INDIRECT function as shown :-

 

amit_bhola_0-1590514580877.png

 

Highlighted

@Peter Bartholomew 

Hi and thanks,

 

but when I changed using INDIRECT a #REF! error resulted:

I'm including the screenshot of the Name Manager.

 

CarlMatDi_0-1590515112581.png

 

Highlighted

@amit_bhola 

Thanks, but when I use INDIRECT a receive a #REF! error.

 

 

CarlMatDi_0-1590515240398.png

 

Highlighted

A detail that I didn't mentioned before is that A3 value was filled using a drop down list that I defined using the data validation window.

Highlighted
Solution

@CarlMatDi , your named range is referring to only one column A3 to A500 (=>excel knows about only one column), but you are mentioning 4 in the Vlookup formula , so excel cannot recognize the 4th column, that is why it is returni ng #REF error

 

For Vlookup to return the value on 4th column starting from column A, your named range should be something like =Movil!$A$3:$D$500   

 

Highlighted
Thank you!! That was the issue!
I didn't notice.