SOLVED
Home

Help breaking down formula

%3CLINGO-SUB%20id%3D%22lingo-sub-506456%22%20slang%3D%22en-US%22%3EHelp%20breaking%20down%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506456%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20new%20to%20the%20community%20and%20somewhat%20new%20to%20the%20formula%20side%20of%20excel.%20Working%20on%20an%20excel%20sheet%20created%20by%20someone%20else.%20I%20would%20like%20help%20breaking%20down%20this%20formula.%20What%20do%20the%20individual%20parts%20do%3F%20This%20formula%20brings%20in%20data%20from%20another%20workbook%2C%20not%20sure%20how%20that%20is%20set%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(HLOOKUP(%24B%241%2CSheet1%2C%24AD6%2CFALSE)%3D%22n%2Fa%22%2CHLOOKUP(%24B%241%2CSheet1%2C%24AD6%2CFALSE)%3D0)%2C%22%22%2CHLOOKUP(%24B%241%2CSheet1%2C%24AD6%2CFALSE))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20input%20greatly%20welcomed%2C%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-506456%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-506604%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20breaking%20down%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506604%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330904%22%20target%3D%22_blank%22%3E%40Colt45%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3DIF(%3CBR%20%2F%3E%26nbsp%3BOR(%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%20HLOOKUP(%24B%241%2CSheet1%2C%24AD6%2CFALSE)%3D%22n%2Fa%22%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%20HLOOKUP(%24B%241%2CSheet1%2C%24AD6%2CFALSE)%3D0%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B)%2C%22%22%2C%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3BHLOOKUP(%24B%241%2CSheet1%2C%24AD6%2CFALSE)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%26nbsp%3B%20)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20the%20HLOOKUP%20part%20is%20trying%20to%20find%20a%20matching%20value%20to%20B1%20across%20the%20columns%20of%20Sheet1%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eit%20will%20then%20bring%20back%20the%20value%20from%20the%20row%20number%20that%20is%20in%20AD6%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20IF%20part%20is%20checking%20if%20that%20returned%20value%20is%20%22n%2Fa%22%20or%200%20in%20which%20case%20it%20returns%20%22%22%20(empty%20cell)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-506613%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20breaking%20down%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506613%22%20slang%3D%22en-US%22%3E%3CP%3EAlso%20Sheet1%20is%20a%20defined%20Name%3C%2FP%3E%0A%3CP%3EIf%20you%20go%20to%20the%20Formulas%20menu%20and%20click%20Defined%20Name%20you'll%20see%20where%20that%20Sheet1%20is%20referring%20to%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20607px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111247iCED9547A0B22C6A9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-542778%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20breaking%20down%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%2C%20the%20names%20manager%20helped%20clear%20the%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-544075%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20breaking%20down%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-544075%22%20slang%3D%22en-US%22%3EGlad%20it%20helped%3C%2FLINGO-BODY%3E
Colt45
New Contributor

Hi,

I am new to the community and somewhat new to the formula side of excel. Working on an excel sheet created by someone else. I would like help breaking down this formula. What do the individual parts do? This formula brings in data from another workbook, not sure how that is set up.

 

=IF(OR(HLOOKUP($B$1,Sheet1,$AD6,FALSE)="n/a",HLOOKUP($B$1,Sheet1,$AD6,FALSE)=0),"",HLOOKUP($B$1,Sheet1,$AD6,FALSE))

 

All input greatly welcomed,

Thank you

4 Replies
Highlighted

Hi @Colt45 

 

=IF(
 OR(

  HLOOKUP($B$1,Sheet1,$AD6,FALSE)="n/a",

  HLOOKUP($B$1,Sheet1,$AD6,FALSE)=0

   ),"",

   HLOOKUP($B$1,Sheet1,$AD6,FALSE)

  )

 

So the HLOOKUP part is trying to find a matching value to B1 across the columns of Sheet1 

it will then bring back the value from the row number that is in AD6

 

The IF part is checking if that returned value is "n/a" or 0 in which case it returns "" (empty cell)

Solution

Also Sheet1 is a defined Name

If you go to the Formulas menu and click Defined Name you'll see where that Sheet1 is referring toimage.png

@Wyn Hopkins

 

Thanks a lot, the names manager helped clear the problem.

Glad it helped
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 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
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies