Formula explanaition please

[Dryver14]

=INDEX(E23:E27,MATCH(G23&H23,INDEX(C23:C27&D23:D27,),0))

sorry peepps I added this question to an old string.

I am facinated by the G23&H23 in the formula above, how many things can you string together and why when you do this does it not look up the combination of the 2 cells within one cell of the index?

 

[pgc01]

... why when you do this does it not look up the combination of the 2 cells within one cell of the index?

It does, you usually write the formula as

=INDEX(E23:E27,MATCH(G23&H23,C23:C27&D23:D27,0))

and it works perfectly well, but since it's an array formula you have to confirm it with CTRL-SHIFT-ENTER instead of just ENTER.

if you replace C23:C27&D23:D27 with INDEX(C23:C27&D23:D27,), the Index() extracts the result of the concatenations into an array and that avoids you having to confirm the formula with CTRL-SHIFT-ENTER.

In conclusion, you don't need the second Index(), you use it for example in a worksheet that can be manipulated by an inexperienced user for whom an array formula or having to confirm a formula with CTRL-SHIFT-ENTER would be confusing.

 

[Dryver14]

Brilliant answer,

Thanks + many

 

[Aladin Akyurek]

Simple concatenation is risky...

Either:

=INDEX(E23:E27,MATCH(G23&"#"&H23,C23:C27&"#"&D23:D27,0))

Or:

=INDEX(E23:E27,MATCH(1,IF(C23:C27=G23,IF(D23:D27=H23,1)),0))

 

[Dryver14]

Dagnammit,

Thought i was bright for a minute there,

What do the "#" 's do and what can happen if they are not there?

 

[Aladin Akyurek]

Dagnammit,

Thought i was bright for a minute there,

What do the "#" 's do and what can happen if they are not there?

Consider...

A1: ABA
B1: D
A2: A
B2: BAD

A1&B1 ==> ABAD
A2&B2 ==> ABAD

With something like # in between:

A1&"#"&B1 ==> ABA#D
A2&"#"&B2 ==> A#BAD

 

[Dryver14]

Thankyou