index function

Question

i've this formula:

=INDEX('1  entry form'!$E$12:$E$27,MATCH(1,($C12='1  entry form'!$B$12:$B$27)*($B12='1  entry form'!$C$12:$C$27),0))

from the formula, i've asked excel to search cells e12 to e27 to matchup the players' names with their corresponding skill levels.

now, how do i insert cells e39 to e54 as part of the search into the formula, which are also from the entry form.

Answer 1

I have also uploaded an excel file for illustration - in case you have looked this up and found any issue in this? Infact the file returns a match from****rows 39 to rows 54 only.

Firstly you need to decide that are you looking to match values in the full range "E12:E54" (then you need to simply extend your range or if you are looking to join 2 non-contiguous ranges of E12:E27" and "E39:E54" and skip the range "E28:E38".

In case you still face problems then please upload your file to enable a solution.

Answer 2

hi amit,

i did press ctrl+shift+enter.

for those people who signed up in rows 12 to rows 27, excel does display the correct corresponding skill levels.

both your & bernie's formulas give me the same result of 0.0 for those people who'd signed up in rows 39 to rows 54.

Answer 3

Please note that my solution considers 2 non-contiguous ranges ie. "E12:E27" and "E39:E54" and any matches found in the range "E28:E38" will not be considered. If you want to consider the full range "E12:E54" then as mentioned by Bernie you need to only extend "E12:E27" to "E12:E54" and similarly with column B.

Reg your query of the formula not working:

The formula is an array formula (Ctrl+Shift+Enter) - this means that after entering the formula press the key F2, then press 3 keys Ctrl+Shift+Enter simultaneously.

I have also uploaded an excel file for illustration - in case you have looked this up and found any issue in this?

In case you still face problems then please upload your file to enable a solution.

Also note that the formula is long because it is joining 2 non-contiguous ranges and further due to the sheet name embedded therein '1  entry form'!.

Firstly you need to decide that are you looking to match values in the full range "E12:E54" (then you need to simply extend your range or if you are looking to join 2 non-contiguous ranges of E12:E27" and "E39:E54" and skip the range "E28:E38".

Regards,

Amit Tandon

Answer 4

yes i did.