SOLVED

"if" and "index match" together

%3CLINGO-SUB%20id%3D%22lingo-sub-2351617%22%20slang%3D%22en-US%22%3E%22if%22%20and%20%22index%20match%22%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2351617%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20someone%20could%20help%20me%20and%20point%20in%20the%20right%20direction.%3C%2FP%3E%3CP%3EPretty%20new%20to%20excel%20and%20specifically%20using%20multiple%20formulas.%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20If%20and%20Index%20match%20in%20the%20same%20formula%20but%20doesn't%20want%20to%20work.%3C%2FP%3E%3CP%3Eso%20i%20have%203%20different%20tables%20in%203%20different%20sheets.%3C%2FP%3E%3CP%3Ethe%20idea%20is%20that%20if%20cell%20A%3D%20%22specific%20name%22%20then%20I%20need%20to%20use%20an%20index%20match%20from%20sheet%202%2C%20otherwise%20I%20need%20to%20use%20index%20match%20from%20sheet%203.%3C%2FP%3E%3CP%3Ewhat%20i%20did%3A%3C%2FP%3E%3CP%3E%3Dif(A%3D%22Name%22%2C%20index(sheet1!B%3AB%2CMATCH(L2%2CSheet1!M%3AM%2C0)%2CINDEx(Sheet2!N%3AN%2CMATCH(L2%2CSheet2!G%3AG%2C0))))%3C%2FP%3E%3CP%3Ethank%20you%20very%20much%20for%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2351617%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2351878%22%20slang%3D%22en-US%22%3ERe%3A%20%22if%22%20and%20%22index%20match%22%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2351878%22%20slang%3D%22en-US%22%3Eyou%20are%20right%2C%20sheet%20one%20is%20where%20i%20am%20creating%20the%20formula%20but%20i%20a%20want%20to%20match%20it%20with%20information%20from%20sheet%202%20and%20sheet%203.%3CBR%20%2F%3Ei%20basically%20want%20to%20know%20if%20A%3DName%2C%20than%20show%20me%20the%20quantity%20from%20sheet%202%20based%20that%20it%20matches%20the%20same%20id%20otherwise%20show%20me%20quantity%20from%20sheet%203%20based%20that%20the%20id%20matches%2C%20does%20this%20help%3F%3CBR%20%2F%3E%3Dif(A1%3D%22Name%22%2C%20index(sheet2!B%3AB%2CMATCH(L2%2CSheet2!M%3AM%2C0)%2CINDEx(Sheet3!N%3AN%2CMATCH(L2%2CSheet3!G%3AG%2C0))))%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2351902%22%20slang%3D%22en-US%22%3ERe%3A%20%22if%22%20and%20%22index%20match%22%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2351902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053100%22%20target%3D%22_blank%22%3E%40infinity94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20should%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(A1%3D%22Name%22%2C%20INDEX(Sheet2!B%3AB%2C%20MATCH(L2%2CSheet2!M%3AM%2C0))%2C%20INDEX(Sheet3!N%3AN%2C%20MATCH(L2%2CSheet3!G%3AG%2C0)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(I%20moved%20one%20closing%20parenthesis)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2352021%22%20slang%3D%22en-US%22%3ERe%3A%20%22if%22%20and%20%22index%20match%22%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2352021%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bit%20doesn't%20want%20to%20work(%3C%2FP%3E%3CP%3Ei%20attached%20screens%20maybe%20this%20will%20be%20more%20clear%20what%20i%20am%20trying%20to%20do.%20Link%20with%20the%20workbook%20and%20pictures.%20thanks%20so%20much%20for%20helping%20me%20out!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AgSn2Fjy1lHAd-Kv73z7vVIF3i4%3Fe%3DG5L5oA%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AgSn2Fjy1lHAd-Kv73z7vVIF3i4%3Fe%3DG5L5oA%3C%2FA%3E%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%22Screenshot%202021-05-13%20at%2018.06.25.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280540i49017929A50200E2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-13%20at%2018.06.25.png%22%20alt%3D%22Screenshot%202021-05-13%20at%2018.06.25.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-13%20at%2018.06.42.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280541iA884FDFF6B3628F9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-13%20at%2018.06.42.png%22%20alt%3D%22Screenshot%202021-05-13%20at%2018.06.42.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-13%20at%2018.06.51.png%22%20style%3D%22width%3A%20952px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F280542iBC5C211352AD0F79%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-13%20at%2018.06.51.png%22%20alt%3D%22Screenshot%202021-05-13%20at%2018.06.51.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2351831%22%20slang%3D%22en-US%22%3ERe%3A%20%22if%22%20and%20%22index%20match%22%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2351831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1053100%22%20target%3D%22_blank%22%3E%40infinity94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20mention%20Sheet%202%20and%20Sheet%203%20but%20your%20formula%20refers%20to%20Sheet1%20and%20Sheet2...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all,

I was wondering if someone could help me and point in the right direction.

Pretty new to excel and specifically using multiple formulas.

I am trying to use If and Index match in the same formula but doesn't want to work.

so i have 3 different tables in 3 different sheets.

the idea is that if cell A= "specific name" then I need to use an index match from sheet 2, otherwise I need to use index match from sheet 3.

what i did:

=if(A="Name", index(sheet1!B:B,MATCH(L2,Sheet1!M:M,0),INDEx(Sheet2!N:N,MATCH(L2,Sheet2!G:G,0))))

thank you very much for help!

6 Replies

@infinity94 

You mention Sheet 2 and Sheet 3 but your formula refers to Sheet1 and Sheet2...

you are right, sheet one is where i am creating the formula but i a want to match it with information from sheet 2 and sheet 3.
i basically want to know if A=Name, than show me the quantity from sheet 2 based that it matches the same id otherwise show me quantity from sheet 3 based that the id matches, does this help?
=if(A1="Name", index(sheet2!B:B,MATCH(L2,Sheet2!M:M,0),INDEx(Sheet3!N:N,MATCH(L2,Sheet3!G:G,0))))

@infinity94 

It should be

 

=IF(A1="Name", INDEX(Sheet2!B:B, MATCH(L2,Sheet2!M:M,0)), INDEX(Sheet3!N:N, MATCH(L2,Sheet3!G:G,0)))

 

(I moved one closing parenthesis)

@Hans Vogelaar it doesn't want to work(

i attached screens maybe this will be more clear what i am trying to do. Link with the workbook and pictures. thanks so much for helping me out!

https://1drv.ms/x/s!AgSn2Fjy1lHAd-Kv73z7vVIF3i4?e=G5L5oA

 

Screenshot 2021-05-13 at 18.06.25.pngScreenshot 2021-05-13 at 18.06.42.pngScreenshot 2021-05-13 at 18.06.51.png

best response confirmed by infinity94 (Occasional Contributor)
Solution

@infinity94 

You missed a comma and a closing parenthesis.

See the attached version.

thank you so much! it works!