If array Formula (different True and False within a If function.

Hi,

Maybe just (array-entered):

=MATCH(TRUE,{"Item One";"Item Two";"Item Three"}="Item Two")

Or, non-array equivalent:

=MATCH(TRUE,INDEX({"Item One";"Item Two";"Item Three"}="Item Two",,))

Regards

Hi,

I suggest you upload your workbook which clearly shows examples of your input cells and the manually entered results you expect to see with a brief explanation as to how you calculate the result if it's not blindingly obvious from the example.

It's not clear to me what you are attempting to do or why an array formula is necessary at all.

This formula

=IF({"Item One";"Item Two";"Item Three"}="Item One",{1;2;3})

returns an "array" of 3 values, i.e. {1;FALSE;FALSE}

so you normally have to use that array in another function that can process arrays, or enter as an "array formula" in 3 cells to see all the results.

If you enter in a single cell you normally just see the first value in the array, in this case 1...., so when you change the formula as you suggested the first value in the array is FALSE and you see that instead

Can you explain what you want to do with that array....or is that the end result?

If you want just 1 and 2 respectively then try using MATCH like this

=MATCH("Item One",{"Item One";"Item Two";"Item Three"},0)

...or if the return number isn't always equal to the position

=HLOOKUP("Item One",{"Item One","Item Two","Item Three";1,2,3},2,0)

so you can change 1,2,3, as required

Try:
=LOOKUP("item1",({"item1","item2","item3"}),{1,2,3})

For LOOKUP to work your item must be in ascending order:
B27:
=LOOKUP(A27,{"Item Five","Item Four","Item One","Item Three","Item Two"},{5,4,1,3,2})

You are welcome.
But still- hard coding is not the best approach.

Please leave reputation points.