formula index match across two sheets spill error

[slochin]

Hello,
Hoping someone might be able to assist. I am trying to use index match across two sheets but receive spill error
=INDEX(sel!A1:A40,MATCH(Sheet1!A1:A40,0))

 

[ETAF]

you are using an array for the match
spill error means the array cannot fill cells because they have data in

What is it you want to do ?
what version of excel?
attach a sample sheet

What version of excel are you using as solutions will be dependant on what version you have and therefore what functions available


A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

 

[slochin]

you are using an array for the match
spill error means the array cannot fill cells because they have data in

What is it you want to do ?
what version of excel?
attach a sample sheet

What version of excel are you using as solutions will be dependant on what version you have and therefore what functions available


A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

 

[slochin]

Hello, Thank you for prompt response. I want to check in sel sheet to see if there is a horse match with the horses in col A on th efirst sheet. I want to see if my selections are matched in the various races. Excel 2024

 

[ETAF]

your example does not show expected results - and SEL sheet has only 1 name which does not match sheet1
see my note above

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

so assuming i understand correctly
try
=IF(COUNTIF(sel!$A$1:$A$1000,TEXTAFTER(A5," "))>0,"yes match","No")
in sheet 1 the names have numbers at the front 1. 8. etc and so will not match
i have used text after to get rid of the numbers

i have added a real horse from sheet 1 into the select sheet

see column AA on shee1

is that what you wanted - if not then a better sample would help with explanation

 

[vletm]

slochin​

As You've answered with #2 reply:
#SPILL! comes because there are something in AA5:AA43
Clear AA5:AA43 and it ... works.

 

[slochin]

your example does not show expected results - and SEL sheet has only 1 name which does not match sheet1
see my note above


so assuming i understand correctly
try
=IF(COUNTIF(sel!$A$1:$A$1000,TEXTAFTER(A5," "))>0,"yes match","No")
in sheet 1 the names have numbers at the front 1. 8. etc and so will not match
i have used text after to get rid of the numbers

i have added a real horse from sheet 1 into the select sheet

see column AA on shee1

is that what you wanted - if not then a better sample would help with explanation

 

[slochin]

Hello,
thank you for your prompt solution

 

[ETAF]

you are welcome