Index match with multiple criteria (text not return)

DEAR ALL,

I HAD FORMULATED INDEX, MATCH FORMULA & RETURN VALUE IS TEXT VALUE. BUT IF TEXT STRING IS VERY LONG, THEN RETURN VALUE IS SHOWS LIKES THIS "############################". PLEASE HELP ME TO RESOLVE THIS ISSUE.
A B
1 Location Text
2 Chennai Chennai, on the Bay of Bengal in eastern India, is the capital of the state of Tamil Nadu.
3 Madurai Madurai is an energetic, ancient city on the Vaigai River in the South Indian state of Tamil Nadu. Its skyline is dominated by the 14 colorful gopurams (gateway towers) of Meenakshi Amman Temple.

INDEX(B2:B3,MATCH("Madurai",A2:A3,0),MATCH("Text",B2:B3,0)

Regards,
Balaji K

Try this

Formula:

Please Login or Register  to view this content.

I cant upload my file due to disabled in attachment content in this forum.

Please find the attached excel file

Hi
Please clarify your question. What is it that you want to get and from what data?

I need data from sheet2 to sheet 1. so I used INDEX, MATCH function for mapping the data. But data is not mapping in Sheet1 and shows error like this ##############. How to resolve this issue.

Really thank you very much. this is the exact thing I need. What changes did u made it in the file. Kindly explain.

Thank you very much..