Break down & define a formula

[MichelleG]

Hello

Please would someone explain each element of this formula - I know what index & match does generally, but what do the { ; --isnumber etc do please?

{=INDEX(D$11:D$32,MATCH(1,(--ISNUMBER(SEARCH(D$1:D$3,A2))),0),0)}

Thanks

 

[Somendra Misra]

Hi MichelleG,

Welcome to the forum.

The {} in the formula means it is entered as array formula. Means after typing formula it is excited with Ctrl+Shift+Enter and not just enter.

than function ISNUMBER() returns true and false so -- is converting those TRUE & False to 1 and 0 so that match can do a match of 1 within the array created by ISNUMBER() to return ordinal position within the array, that will be used as row position for INDEX function.

Regards!

 

[MichelleG]

Hi MichelleG,

Welcome to the forum.

The {} in the formula means it is entered as array formula. Means after typing formula it is excited with Ctrl+Shift+Enter and not just enter.

than function ISNUMBER() returns true and false so -- is converting those TRUE & False to 1 and 0 so that match can do a match of 1 within the array created by ISNUMBER() to return ordinal position within the array, that will be used as row position for INDEX function.

Regards!

Thank you, that helps a little. I'm very new to formulas so still trying to understand how to define what they're doing let alone how to build them. Is this one effectively saying to match the relevant cell within D11:D32, to one of the cells in D1 to D3? What does A2 do in this ?
Thanks very much

 

[Somendra Misra]

See, Search is search a value in D1:D3 in A2.
For example if you have D1: A D2:B D3:C and A2: B Will have so SEARCH(D$1:D$3,A2) will return an array because you are telling excel that this is an array formula by Ctrl+Shift+Enter so result will be {"#Value;1;#Value}.
NOW is number will return {FALSE;TRUE;FALSE} now -- will do conversion {0;1;0} and match will give 2
so INDEX will give 2nd row from array D$11:D$32 as result.

I think now its clear. You can use F9 to evaluate each part of formula to understand it.

Regards!

 

[Xiq]

Hi MichelleG,

Not exactly.

• D1:D3 can have lets say {"Cow","Chicken","Dog"} and you ask if any of these words can be found in A2 "The cow and the chicken are eating" .
• Either it finds something (then it returns the position of that word where it can be found in A2) or not (it will give an error). So {5,17,na()}.
• The next step is to convert the result in an array either 1's or 0's, this is done by the ISNUMBER function. So {1,1,0}
• Now you use the MATCH function to look for the first 1 it can find

 

[Somendra Misra]

@Xiq .....Perfect timing sir. Both the post are on same time.

 

[MichelleG]

Thank you both. I find the techy speak on even the F9 doesn't help me, the animal story makes it much clearer Thanks so much

 

[MichelleG]

what if, instead of returning the position where the word can be found, I want to return a corresponding value, the way that vlookup does?

 

[Somendra Misra]

VlookUp also matches the lookup value in lookup array and return row number & finally at that row whatever column you mention you get the value.

 

[Xiq]

what if, instead of returning the position where the word can be found, I want to return a corresponding value, the way that vlookup does?

You already do this with the index function. But in the example formula you posted, this is done incorrectly (because you can only have 3 possible matches, instead of 22...).

{=INDEX({Result if Cow, result if Chicken, result if Dog},MATCH(1,(--ISNUMBER(SEARCH({"Cow","Chicken","Dog"},"The cow and the chicken are eating"))),0),0)}