SOLVED

Vlookup does not work when based on formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2656629%22%20slang%3D%22en-US%22%3EVlookup%20does%20not%20work%20when%20based%20on%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2656629%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20my%20vlookup%20does%20not%20work%20when%20the%20value%20to%20lookup%20is%20the%20result%20of%20a%20formula.%20In%20the%20example%20below%2C%20the%20Transfert%20Detail%20column%20is%20the%20result%20of%20a%20vlookup.%20The%20subsequent%20Vlookup%20in%20Campaign%20that%20references%20the%20AK%20column%20in%20the%20following%20formula%20gives%20me%20a%20%23REF!%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP((AK3)%2CTransfer%2C25%2CFALSE)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Elva_Tanguerre_0-1629214463457.png%22%20style%3D%22width%3A%20662px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303793i947CEF1702CC3206%2Fimage-dimensions%2F662x86%3Fv%3Dv2%22%20width%3D%22662%22%20height%3D%2286%22%20role%3D%22button%22%20title%3D%22Elva_Tanguerre_0-1629214463457.png%22%20alt%3D%22Elva_Tanguerre_0-1629214463457.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20fix%20this%3F%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2656629%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-2656719%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20does%20not%20work%20when%20based%20on%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2656719%22%20slang%3D%22en-US%22%3EIf%20possible%20plz%20share%20the%20WB%20with%20us%20since%20the%20screen%20shot%20is%20not%20enough%20to%20understand%20the%20issue%20!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2656757%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20does%20not%20work%20when%20based%20on%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2656757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F996310%22%20target%3D%22_blank%22%3E%40Elva_Tanguerre%3C%2FA%3E%26nbsp%3BFirst%20of%20all%2C%20you%20can%20get%20rid%20of%20the%20brackets%20surrounding%20AK3.%20Then%20the%20%23REF%20error%20suggests%20that%20you%20are%20attempting%20to%20return%20a%20column%20that%20does%20not%20exist%20in%20the%20lookup%20range.%20The%20lookup%20range%2C%20in%20your%20case%2C%20is%20a%20named%20range%20called%20%22Transfer%22.%20Are%20you%20sure%20it%20has%20the%20Transfer%20Detail%20in%20the%20first%20column%20and%20that%20is%20has%20at%20least%2025%20columns%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, my vlookup does not work when the value to lookup is the result of a formula. In the example below, the Transfert Detail column is the result of a vlookup. The subsequent Vlookup in Campaign that references the AK column in the following formula gives me a #REF!:

 

=VLOOKUP((AK3),Transfer,25,FALSE)

Elva_Tanguerre_0-1629214463457.png

 

How can I fix this? Thanks!

5 Replies
best response confirmed by Elva_Tanguerre (Occasional Contributor)
Solution
If possible plz share the WB with us since the screen shot is not enough to understand the issue !!

@Elva_Tanguerre First of all, you can get rid of the brackets surrounding AK3. Then the #REF error suggests that you are attempting to return a column that does not exist in the lookup range. The lookup range, in your case, is a named range called "Transfer". Are you sure it has the Transfer Detail in the first column and that is has at least 25 columns?

Thanks, but Riny_van_Eekelen has solved it.
It is indeed the 25th column in the sheet, but the 2nd in the name range... Duh! Thanks!
Perhaps you can remark the "best answer"