Home

Excel VLOOKUP function missing REF

%3CLINGO-SUB%20id%3D%22lingo-sub-874842%22%20slang%3D%22en-US%22%3EExcel%20VLOOKUP%20function%20missing%20REF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-874842%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20pulling%20an%20excel%20from%20online%20and%20I'm%20trying%20to%20figure%20out%20why%20the%20formulas%20work%2C%20but%20don't%20calculate.%20When%20I%20go%20to%20NAME%20MANAGER%2C%20I%20see%20that%20I%20am%20missing%20something%3A%3CBR%20%2F%3E%22%3D'INPUT%20DATA'!%23REF!%22%20-more%20or%20less.%3C%2FP%3E%3CP%3EThe%20worksheet%20is%20the%20main%20(as%20it%20says)%20sheet%20to%20input%20data.%20I%20tried%20to%20input%20other%20reference%20tables%2C%20but%20I%20am%20not%20having%20any%20progress.%20How%20do%20I%20troubleshoot%20this%3F(The%20person%20who%20made%20it%20originally%20has%20retired%20since%20posting%20so%20I%20don't%20know%20how%20to%20get%20a%20hold%20of%20them.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-874842%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-874896%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLOOKUP%20function%20missing%20REF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-874896%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415118%22%20target%3D%22_blank%22%3E%40chellpish%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20the%20formula%20that%20doesn't%20work%3C%2FP%3E%3CP%3E%3DIF(B4%26gt%3B%22A%22%2CIF(G4%26lt%3B%26gt%3B%22P%22%2CLOOKUP(G4%2CINDIRECT(LOOKUP(G%242%2Ceventlookup)%26amp%3B%22SCALE%22)%2CINDIRECT(LOOKUP(G%242%2Ceventlookup)%26amp%3B%24B4%26amp%3BTEXT(LOOKUP(%24C4%2Cagelookup)%2C%22%23%23%22)))%2C%22P%22)%2C%22%20%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20formula%20that%20does%20work%3C%2FP%3E%3CP%3E%3DIF(B4%26gt%3B%22A%22%2CIF(I4%26lt%3B%26gt%3B%22NOGO%22%2CIF(I4%26lt%3B%26gt%3B%22GO%22%2CLOOKUP(IF(I4%26gt%3B%3D1000%2CTIME(0%2CLEFT(I4%2C2)%2CRIGHT(I4%2C2)%2B5)%2CTIME(0%2CLEFT(I4%2C1)%2C%20RIGHT(I4%2C2)%2B5))%2C%20INDIRECT(LOOKUP(I%242%2Ceventlookup)%26amp%3B%22SCALE%22)%2CINDIRECT(LOOKUP(I%242%2Ceventlookup)%26amp%3B%24B4%26amp%3BTEXT(LOOKUP(%24C4%2Cagelookup)%2C%22%23%23%22)))%2C%22GO%22)%2C%22NOGO%22)%2C%22%20%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875202%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VLOOKUP%20function%20missing%20REF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875202%22%20slang%3D%22en-US%22%3Eit's%20because%20of%20a%20compatibility%20thing%20with%20lotus.%3CBR%20%2F%3E%3A%3Afacepalm%3A%3A%3CBR%20%2F%3Enow%20I%20just%20have%20to%20figure%20out%20how%20to%20rewrite%20the%20formulas%20to%20reference%20the%20underscored%20table%20names.%3C%2FLINGO-BODY%3E
chellpish
New Contributor

I'm pulling an excel from online and I'm trying to figure out why the formulas work, but don't calculate. When I go to NAME MANAGER, I see that I am missing something:
"='INPUT DATA'!#REF!" -more or less.

The worksheet is the main (as it says) sheet to input data. I tried to input other reference tables, but I am not having any progress. How do I troubleshoot this?(The person who made it originally has retired since posting so I don't know how to get a hold of them.)

2 Replies

@chellpish 

this is the formula that doesn't work

=IF(B4>"A",IF(G4<>"P",LOOKUP(G4,INDIRECT(LOOKUP(G$2,eventlookup)&"SCALE"),INDIRECT(LOOKUP(G$2,eventlookup)&$B4&TEXT(LOOKUP($C4,agelookup),"##"))),"P")," ")

 

 

the formula that does work

=IF(B4>"A",IF(I4<>"NOGO",IF(I4<>"GO",LOOKUP(IF(I4>=1000,TIME(0,LEFT(I4,2),RIGHT(I4,2)+5),TIME(0,LEFT(I4,1), RIGHT(I4,2)+5)), INDIRECT(LOOKUP(I$2,eventlookup)&"SCALE"),INDIRECT(LOOKUP(I$2,eventlookup)&$B4&TEXT(LOOKUP($C4,agelookup),"##"))),"GO"),"NOGO")," ")

 

 

it's because of a compatibility thing with lotus.
::facepalm::
now I just have to figure out how to rewrite the formulas to reference the underscored table names.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies