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
Highlighted
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
Highlighted

@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")," ")

 

 

Highlighted
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.