SOLVED

"if" and "index match" together

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!