How do you use { and } formula in Index, as when I use { or } the formula gives error

I'm not sure I understand the question. In most formulas you use parenthesis "(" and ")", not "{".

For INDEX, the syntax is:

INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array.

INDEX(reference,row_num,column_num,area_num) returns a reference to specified cells within reference.

Can you provide an example of the formula that is giving you trouble?

Quote:

---

Originally Posted by mdosh01

I'm not sure I understand the question. In most formulas you use parenthesis "(" and ")", not "{".

For INDEX, the syntax is:

INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array.

INDEX(reference,row_num,column_num,area_num) returns a reference to specified cells within reference.

Can you provide an example of the formula that is giving you trouble?

---

Someone provided me with an help with a formula as

{=INDEX(Sheet1!$C$2:$C$5,MATCH(A3,IF(Sheet1!$B$2:$ B$5=B3,Sheet1!$A$2:$A$5),0))}

This formula was given in an example with an excel sheet.

Here the { and } does not work, when I type them it gives an error, and when I do not type { and } the formula does not work.

But the person who help me has this same formula written in excel sheet with { and } and the formula works for him.

OK, so this is the array syntax. Your array is C2:C5 in Sheet1. The row number is determined by the Match statement and the column is 0. Since it is 0, INDEX will return the entire column.

The MATCH function, that will determine the row, says to lookup the value A3 in the array returned by the IF statement. The IF statement says if the array B2:B5 is equal to B3, then return the array A2:A5 otherwise "false" will be returned.

I think the IF statement is the problem. I don't know how an array of 4 numbers can equal a single number. Without knowing the values in the arrays (a2:a5, b2:b5, and c2:c5) I can't say what the intent of the formula would be.

When getting complex formulas like this, break them down into their smaller functions. In this case, put the IF statement in its own cell, say C10 as '=if(B2:B5=B3,A2:A5)'

Once you get that to work, I think the rest will be fine. I did notice you have a space in the middle of your formula also, indicated below by {space} -
=INDEX(Sheet1!$C$2:$C$5,MATCH(A3,IF(Sheet1!$B$2:${ space} B$5=B3,Sheet1!$A$2:$A$5),0))

Formulas cannot contain spaces.

Hope this helps.

Just to add, its likely they included the French brackets {} to enclose the formula, but they werent' meant to be included when entered in the cell.

Thanks Scott. I forgot the obvious issue.