Insert cell if found in the sheet (index and match)

[Anna657]

Hi

I have been trying to insert a number, if the village is found in a specific column in another sheet using INDEX and MATCH. However, it only seems to work if I only use one row and not multiple rows.

When I use the entire sheet from A3:X24 the result comes out as #N/A. If I only choose one row in the formula, i.e. A3:X3, the result is 4 (which is correct). However, I need to include all the villages in the column in order to get the correct value for all my cells.

I'm not very used to Excel so maybe there is something I have overlooked. Hope you can help me

 

[Fluff]

Hi & welcome to MrExcel.
Will the village only occur once in the Districts sheet?

 

[Anna657]

Thank you! And yes - unless I have made a mistake

 

[Fluff]

Ok, how about

=MAX(MMULT(--(Districts!$A$3:$X$24=M2),SEQUENCE(24)))

 

[Anna657]

Yes, that worked - thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.