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

Did you re-enter the formula after editing it by using Ctrl-Shift-Enter?  It is an array-formula and requires special entry.

It is also possible that what you actually need is this:

=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)*($D12='1  entry form'!$E$39:$E$54),0))

also entered using Ctrl-Shift-Enter - but you need to use another comparison cell: I used D12 to match the entries in E39:E54.

Answer 2

hi amit,

thx for your help. the formula is very long, but i'm still getting 0.0 instead of the correct skill levels.

the players that sign up in the first section of the entry form (E12:E27) has the correct skill levels displayed.

it's only the players that sign up in the second section of the entry form(E39:E54) is where excel displays the 0.0 as the skill levels.

Answer 3

hi bernie,

i changed the 27s to 54s, but i'm still getting 0.0 instead of the correct skill levels.

Answer 4

If you want to join non-contiguous ranges so as not to consider rows 28 to 38, then use below array formula (Ctrl+Shift+Enter) - 

=INDEX(IF((ROW('1  entry form'!$E$12:$E$54)>=12)*(ROW('1  entry form'!$E$12:$E$54)<=27)+(ROW('1  entry form'!$E$12:$E$54)>=39)*(ROW('1  entry form'!$E$12:$E$54)<=54),'1  entry form'!$E$12:$E$54),MATCH(1,($C12=IF((ROW('1  entry form'!$B$12:$B$54)>=12)*(ROW('1  entry form'!$B$12:$B$54)<=27)+(ROW('1  entry form'!$B$12:$B$54)>=39)*(ROW('1  entry form'!$B$12:$B$54)<=54),'1  entry form'!$B$12:$B$54))*($B12=IF((ROW('1  entry form'!$C$12:$C$54)>=12)*(ROW('1  entry form'!$C$12:$C$54)<=27)+(ROW('1  entry form'!$C$12:$C$54)>=39)*(ROW('1  entry form'!$C$12:$C$54)<=54),'1  entry form'!$C$12:$C$54)),0))

The formula appears long also due to the embedded sheet names.

You may also download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/NonContiguousRanges_Join_Excel.xlsx

Regards,

Amit Tandon

www.globaliconnect.com

http://twitter.com/AmitTandonExcel

Answer 5

Try just changing all the 27s in the formula to 54s - as long as the values in rows 28 to 38 cannot match your criteria, it should work fine.