SOLVED

Need help with formula as not sure what am doing wrong

%3CLINGO-SUB%20id%3D%22lingo-sub-2375689%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20formula%20as%20not%20sure%20what%20am%20doing%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375689%22%20slang%3D%22en-US%22%3E%3CP%3ECould%20someone%20help%20please%3F%20The%20formula%20for%20column%20I%20is%3C%2FP%3E%3CP%3E%3DIF(ISNA(VLOOKUP(H11%2C%24Y%2410%3A%24Z%2434%2C2%2CFALSE))%2C%22%22%2C(VLOOKUP(H11%2C%24Y%2410%3A%24Z%2434%2C2%2CFALSE)))%3C%2FP%3E%3CP%3EWhat%20I%20need%20and%20am%20struggling%20to%20work%20out%20is%20I%20need%20Column%20J%20to%20auto%20populate%20with%20text%20from%20AB10%20through%20AB34%20when%20column%20I%20auto%20populates.%3C%2FP%3E%3CP%3ETo%20make%20it%20even%20worse%2C%20I%20also%20need%20coloumn%20E%20to%20auto%20populate%20with%20text%20from%20AB39%20through%20AB43%20when%20column%20I%20auto%20populates.%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%22Stephen010_0-1621603574181.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F282648i2B3BBD9A40CB02ED%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Stephen010_0-1621603574181.png%22%20alt%3D%22Stephen010_0-1621603574181.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2375689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375715%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20as%20not%20sure%20what%20am%20doing%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375715%22%20slang%3D%22en-US%22%3EThe%20screen%20capture%20is%20very%20small.%20If%20you're%20able%20to%20upload%20the%20workbook%20(even%20w%2Fdummy%20data)%2C%20I%20can%20take%20a%20look.%20Thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2377260%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20as%20not%20sure%20what%20am%20doing%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059994%22%20target%3D%22_blank%22%3E%40Stephen010%3C%2FA%3E%26nbsp%3B%20wrote%3A%20%60%60I%20need%20Column%20J%20to%20auto%20populate%20with%20text%20from%20AB10%20through%20AB34%20when%20column%20I%20auto%20populates%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20no%20idea%2C%20since%20the%20image%20is%20completely%20unreadable%2C%20even%20(especially)%20when%20magnified%2C%20and%20I%20see%20no%20references%20to%20column%20AB%20in%20your%20unsuccessful%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20perhaps%20that's%20the%20problem%3A%20no%20reference%20to%20column%20AB.%26nbsp%3B%20Perhaps%20the%20following%20in%20J11%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%3CFONT%20color%3D%22%23FF0000%22%3EIF(I11%3D%22%22%2C%20%22%22%2C%3C%2FFONT%3E%20%3CFONT%20color%3D%22%23FF0000%22%3EIFERROR%3C%2FFONT%3E(VLOOKUP(H11%2C%20%24Y%2410%3A%24%3CFONT%20color%3D%22%23FF0000%22%3EAB%3C%2FFONT%3E%2434%2C%20%3CFONT%20color%3D%22%23FF0000%22%3E4%3C%2FFONT%3E%2C%20FALSE)%2C%20%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20version%20of%20Excel%20does%20not%20support%20IFERROR%2C%20you're%20stuck%20with%20ISNA%2C%20as%20you%20used%20it%20--%20but%20without%20the%20unnecessary%20parenthesis%20that%20hinder%20readability.%26nbsp%3B%20To%20wit%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%3CFONT%20color%3D%22%23FF0000%22%3EIF(I11%3D%22%22%2C%20%22%22%2C%3C%2FFONT%3E%20%3CFONT%20color%3D%22%23FF0000%22%3EIF%3C%2FFONT%3E(ISNA(VLOOKUP(H11%2C%20%24Y%2410%3A%24%3CFONT%20color%3D%22%23FF0000%22%3EAB%3C%2FFONT%3E%2434%2C%20%3CFONT%20color%3D%22%23FF0000%22%3E4%3C%2FFONT%3E%2C%20FALSE))%2C%20%22%22%2C%3C%2FP%3E%3CP%3EVLOOKUP(H11%2C%20%24Y%2410%3A%24%3CFONT%20color%3D%22%23FF0000%22%3EAB%3C%2FFONT%3E%2434%2C%20%3CFONT%20color%3D%22%23FF0000%22%3E4%3C%2FFONT%3E%2C%20FALSE)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059994%22%20target%3D%22_blank%22%3E%40Stephen010%3C%2FA%3E%26nbsp%3B%20wrote%3A%20%60%60I%20also%20need%20coloumn%20E%20to%20auto%20populate%20with%20text%20from%20AB39%20through%20AB43%20when%20column%20I%20auto%20populates%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20even%20less%20of%20an%20idea%2C%20since%20we%20do%20not%20know%20what%20cell%20to%20reference%20in%20place%20of%20H11%2C%20if%20anything%20else.%26nbsp%3B%20Wild%20guess%3A%20perhaps%20the%20following%20in%20E11%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%3CFONT%20color%3D%22%23FF0000%22%3EIF(I11%3D%22%22%2C%20%22%22%2C%3C%2FFONT%3E%20%3CFONT%20color%3D%22%23FF0000%22%3EIFERROR%3C%2FFONT%3E(VLOOKUP(%3CFONT%20color%3D%22%23FF0000%22%3EG11%3C%2FFONT%3E%2C%20%24Y%2410%3A%24%3CFONT%20color%3D%22%23FF0000%22%3EAB%3C%2FFONT%3E%2434%2C%20%3CFONT%20color%3D%22%23FF0000%22%3E4%3C%2FFONT%3E%2C%20FALSE)%2C%20%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOTOH%2C%20if%20the%20VLOOKUP%20should%20be%20exactly%20the%20same%20--%20and%20E11%20should%20have%20the%20same%20result%20as%20J11%20(!)%20--%20simply%20write%20in%20E11%20(!!)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3DJ11%3C%2FFONT%3E%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-2377298%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20as%20not%20sure%20what%20am%20doing%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3BI%20kept%20getting%20the%20page%20to%20register%20again%20while%20trying%20to%20login%20so%20now%20responding%20with%20Stephen010610.%3C%2FP%3E%3CP%3EAnyway%2C%20table%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2377300%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20as%20not%20sure%20what%20am%20doing%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3Bthanks%20for%20your%20reply%2C%20I%20am%20responding%20as%20Stephen010610%20as%20when%20I%20tried%20to%20login%2C%20all%20I%20got%20was%20the%20register%20page.%20anyway%20table%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2377751%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20as%20not%20sure%20what%20am%20doing%20wrong%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377751%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059994%22%20target%3D%22_blank%22%3E%40Stephen010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3Ein%20J%0A%3DIFNA(VLOOKUP(%24I11%2C%24Z%2410%3A%24AB%2434%2C3%2C0)%2C%22%22)%0A%0Ain%20E%0A%3DIFNA(LOOKUP(%24H11%2C%7B0%2C4%2C8%2C15%2C21%7D%2C%24AB%2438%3A%24AB%2442)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Could someone help please? The formula for column I is

=IF(ISNA(VLOOKUP(H11,$Y$10:$Z$34,2,FALSE)),"",(VLOOKUP(H11,$Y$10:$Z$34,2,FALSE)))

What I need and am struggling to work out is I need Column J to auto populate with text from AB10 through AB34 when column I auto populates.

To make it even worse, I also need coloumn E to auto populate with text from AB39 through AB43 when column I auto populates.

 

Stephen010_0-1621603574181.png

 

7 Replies
The screen capture is very small. If you're able to upload the workbook (even w/dummy data), I can take a look. Thank you.

[.... deleted by me ....]

 

Sorry, I misread your original posting.  You clearly wrote:  ``The formula for column I is =IF(ISNA(VLOOKUP(H11,$Y$10:$Z$34,2,FALSE)) ....``

@Patrick2788 I kept getting the page to register again while trying to login so now responding with Stephen010610.

Anyway, table attached.

@Joe User thanks for your reply, I am responding as Stephen010610 as when I tried to login, all I got was the register page. anyway table attached.

best response confirmed by allyreckerman (Microsoft)
Solution

@Stephen010 

As variant

in J
=IFNA(VLOOKUP($I11,$Z$10:$AB$34,3,0),"")

in E
=IFNA(LOOKUP($H11,{0,4,8,15,21},$AB$38:$AB$42),"")

@Sergei Baklan Hi Sergei, thanks for the reply. Unfortunately the formula provided for J did not work but the formula for E did work. Then I thought what if I recalculate the formula for J along similar lines to E but change the cell letter/number, and it worked so thanks for your assistance, my problem has been resolved.

 

Stephen

@Stephen010610 

Stephen, glad to know you sorted this out, thank you for the feedback.