Return first cell in row if condition is met.

%3CLINGO-SUB%20id%3D%22lingo-sub-3300840%22%20slang%3D%22en-US%22%3EReturn%20first%20cell%20in%20row%20if%20condition%20is%20met.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300840%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20not%20a%20pro%20in%20excel%20I'm%20just%20experimenting%20some%20formulas%20for%20a%20recipe%20organiser.%3C%2FP%3E%3CP%3EBasically%2C%20I%20want%20to%20return%20the%20recipe%20name%20of%20all%20recipes%20containing%20one%20specific%20ingredients%2C%20just%20one.%3C%2FP%3E%3CP%3EI've%20tried%20with%20Vlookup%20but%20it%20doesn't%20work%20since%20since%20the%20lookup%20value%20is%20not%20on%20the%20first%20row.%3C%2FP%3E%3CP%3EMy%20table%20look%20like%20this%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-05-02%20at%2021.57.32.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F368667iA4F255C6E02FE4E0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-05-02%20at%2021.57.32.png%22%20alt%3D%22Screenshot%202022-05-02%20at%2021.57.32.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAny%20ideas%20how%20can%20I%20make%20it%20possible%3F%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3300840%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-3300879%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20first%20cell%20in%20row%20if%20condition%20is%20met.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20but%20it's%20not%20working%20on%20my%20version%20of%20excel%2C%20I%20will%20investigate%20with%20INDEX%2BMATCH%20to%20see%20if%20I%20can%20crack%20the%20code%20%3A))%20Thanks%20tho%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300856%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20first%20cell%20in%20row%20if%20condition%20is%20met.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1379158%22%20target%3D%22_blank%22%3E%40Gabitsu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTEXTJOIN(%22%3B%22%2CTRUE%2CIFERROR(INDEX(A2%3AA7%2CIF(MMULT(N(B2%3AE7%3DG2)%2CROW(1%3A4)%5E0)*ROW(1%3A6)%26gt%3B0%2CMMULT(N(B2%3AE7%3DG2)%2CROW(1%3A4)%5E0)*ROW(1%3A6)%2C%22%22))%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20works%20in%20my%20Excel%20online%20sheet.%20However%20it's%20only%20possible%20to%20apply%20this%20formula%20if%20you%20work%20with%20Office365%20or%202021%20or%20Excel%20online.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm not a pro in excel I'm just experimenting some formulas for a recipe organiser.

Basically, I want to return the recipe name of all recipes containing one specific ingredients, just one.

I've tried with Vlookup but it doesn't work since since the lookup value is not on the first row.

My table look like this:Screenshot 2022-05-02 at 21.57.32.png

Any ideas how can I make it possible?

Thanks!

2 Replies

@Gabitsu 

=TEXTJOIN(";",TRUE,IFERROR(INDEX(A2:A7,IF(MMULT(N(B2:E7=G2),ROW(1:4)^0)*ROW(1:6)>0,MMULT(N(B2:E7=G2),ROW(1:4)^0)*ROW(1:6),"")),""))

This works in my Excel online sheet. However it's only possible to apply this formula if you work with Office365 or 2021 or Excel online.

@Quadruple_Pawn 

Thanks, but it's not working on my version of excel, I will investigate with INDEX+MATCH to see if I can crack the code :)) Thanks tho