 SOLVED

# "if" and "index match" together

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

# Re: "if" and "index match" together

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

# Re: "if" and "index match" together

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))))

# Re: "if" and "index match" together

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)

# Re: "if" and "index match" together

@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!   best response confirmed by infinity94 (Occasional Contributor)
Solution

# Re: "if" and "index match" together

You missed a comma and a closing parenthesis.

See the attached version.

# Re: "if" and "index match" together

thank you so much! it works!